Re: [libreoffice-users] Function acting on range
I was meaning that if I have 10 sheets, I can refer to them by position 1 to 10 rather than by name. =SHEET($Sheet19.B28) returns 29. I was wondering if I could refer to this cell something like ='29'.B28 On 18/03/2023 22:20, ady wrote: If by "sheets by position" you mean things like "the next one, whichever the next worksheet would be", you can. I don't know whether you actually need this feature – please allow me to doubt it – but there is such a thing in LibreOffice Calc. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Function acting on range
If by "sheets by position" you mean things like "the next one, whichever the next worksheet would be", you can. I don't know whether you actually need this feature – please allow me to doubt it – but there is such a thing in LibreOffice Calc. Beware: if you need compatibility with other spreadsheet software, I wouldn't recommend using this feature. Not every spreadsheet software (and/or file format) supports this feature. Moreover, before investing too much time on this, you should test this with a simple basic case, save it, close the software and reopen the test file again, in order to check whether everything works as expected. You have been warned! In LibreOffice Calc, worksheets can be addressed by relative references. For cells, you add a "$" symbol for columns, rows, or both for absolute references. This is quite common, in every spreadsheet software and file format. For worksheets in LO Calc, when you precede a worksheet name with the same "$" symbol you make its reference absolute. Absolute worksheet references are the default (and only) mode for most spreadsheet tools and file formats (so they don't use any additional symbol). In Calc – I cannot emphasize that point enough – if you use the name without the preceding "$" symbol, its address is relative. Please "play" with this feature before using it in important work. For instance, use relative references for multiple (i.e. more than 2) worksheets, save the file, then move (all) worksheets around and see the resulting formulas and actual calculation results. Copy worksheets. Delete worksheets. Before and after each action, please pay attention to the resulting changes, in order to understand the effect of relative references. You should have clear knowledge of how relative and absolute references affect the results of actions such as move, copy and delete, before attempting to use them. Should I add "Use this feature at your own risk"? You have been warned, again. Don't blame anyone else! Using relative references for worksheets should not be used by inexperienced users, and it should only be used for specific purposes, not as a norm. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Function acting on range
On 17/03/2023 13:08, Michael D. Setzer II wrote:
On 16 Mar 2023 at 23:29, Regina Henschel wrote:
Subject:Re: [libreoffice-users] Function acting on
range
To: users@global.libreoffice.org
From: Regina Henschel
Date sent: Thu, 16 Mar 2023 23:29:10 +0100
Hi Michael,
Michael D. Setzer II schrieb am 16.03.2023 um 22:45:
On 16 Mar 2023 at 18:22, ady wrote:
From:ady
Date sent:Thu, 16 Mar 2023 18:22:56 -0300
Subject:Re: [libreoffice-users] Function acting on
range
To:Users
Hi Regina,
But you can use {=AVERAGE({ABS($Sheet1.B1:$Sheet1.B30)} for example.
Besides the typo, for that to work it would need to be all in the same
worksheet, which is contrary to the initial setup.
ISTM that using SUM() and COUNT() would be simpler, if the data is
already with the layout as described, in multiple sheets.
Don't see how just using SUM() and COUNT() would
work.
Sum() and Count() work an ranges which include several sheets. So if you
did not need Abs(), then Sum()/Count() would give the average.
Therefore the problem here is, that Steve needs the Abs() function, for
what ever reason.
Unfortunately Sumif() does neither work on ranges over several sheets.
So the solution
(Sumif(myRange;">0")-Sumif(myRange;"<0")/Count(myRange) will not work,
if myRange goes over several sheets.
Interesting. Don't know why sum() and count() have no
problem handling cells across sheets but sumif() doesn't?
Just did my test using cells on single sheet and it had no
problem, but then did try it with using b2 on sheet1 thru
sheet6, and get error. Do a lot of things with calc, but
not things like that.
In my test, seems one would have to individually do each
cell. In my case I did 6, but his would require 19??
Actually 29 sheets. I couldn't find a way to address sheets by position
rather than name. The first sheet is 1, the last 19 and the ones in the
middle have various names.
=AVERAGE(ABS(Sheet1.B2),ABS(Sheet2.B2),ABS(Sheet
3.B2),ABS(Sheet4.B2),ABS(Sheet5.B2),ABS(Sheet6.B2))
Don't have quick access to see if its SUMIF would
support ranges. Have used spreadsheets going back to
visicalc on Apple, Lotus 1 on PC then thru version of
Lotus, Quattro Pro, and then Open and Libre Calc.
Wonder why the Sumif doesn't use similar base as sum?
Thanks for info.
Inside one sheet there is no problem with abs or sumif using ranges.
Kind regards,
Regina
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
++
Michael D. Setzer II - Computer Science Instructor
(Retired)
mailto:mi...@guam.net
mailto:msetze...@gmail.com
Guam - Where America's Day Begins
G4L Disk Imaging Project maintainer
http://sourceforge.net/projects/g4l/
++
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Function acting on range
> > ISTM that using SUM() and COUNT() would be simpler, if the data is > > already with the layout as described, in multiple sheets. > Don't see how just using SUM() and COUNT() would work. Assuming the use of > ABS was wanting to change negative values to positive values a simple sum of > numbers would give a value different from the sum of absolute values. I apologize. I wasn't clear – I didn't have enough time in order to be specific when I wrote that email, and that sentence I wrote was most probably adding to the confusion, unintentionally. I don't want to add more confusion, so let me (try to) be succinct and avoid unneeded extras. As Steve presented the case and the specific layout... _ ABS() needs to calculate each value on its own. The only way to use ABS() on a range, as far as I know, is to use it as array formula and only within the same worksheet, and there is no need (nor advantage) in this case. _ AVERAGE() can be used with multiple worksheets and in 3D references. There is no need for array formulas in this case either, and it would probably be counterproductive. _ To be clear, just in case, 3D references is not the same as array formulas. _ There are alternative ways to resolve this, but *with the layout as presented*, Steve's initial assumption was correct. ABS() expects one cell (or value) as argument, while AVERAGE() can be used as intended. My apologies again for adding to the confusion. Best regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Function acting on range
On 16 Mar 2023 at 23:29, Regina Henschel wrote:
Subject:Re: [libreoffice-users] Function acting on
range
To: users@global.libreoffice.org
From: Regina Henschel
Date sent: Thu, 16 Mar 2023 23:29:10 +0100
> Hi Michael,
>
> Michael D. Setzer II schrieb am 16.03.2023 um 22:45:
> > On 16 Mar 2023 at 18:22, ady wrote:
> >
> >
> > From:ady
> > Date sent:Thu, 16 Mar 2023 18:22:56 -0300
> > Subject:Re: [libreoffice-users] Function acting on
> > range
> > To:Users
> >
> >
> >> Hi Regina,
> >>
> >>> But you can use {=AVERAGE({ABS($Sheet1.B1:$Sheet1.B30)} for example.
> >>
> >> Besides the typo, for that to work it would need to be all in the same
> >> worksheet, which is contrary to the initial setup.
> >>
> >> ISTM that using SUM() and COUNT() would be simpler, if the data is
> >> already with the layout as described, in multiple sheets.
> >
> >
> > Don't see how just using SUM() and COUNT() would
> > work.
>
> Sum() and Count() work an ranges which include several sheets. So if you
> did not need Abs(), then Sum()/Count() would give the average.
>
> Therefore the problem here is, that Steve needs the Abs() function, for
> what ever reason.
>
> Unfortunately Sumif() does neither work on ranges over several sheets.
> So the solution
> (Sumif(myRange;">0")-Sumif(myRange;"<0")/Count(myRange) will not work,
> if myRange goes over several sheets.
>
Interesting. Don't know why sum() and count() have no
problem handling cells across sheets but sumif() doesn't?
Just did my test using cells on single sheet and it had no
problem, but then did try it with using b2 on sheet1 thru
sheet6, and get error. Do a lot of things with calc, but
not things like that.
In my test, seems one would have to individually do each
cell. In my case I did 6, but his would require 19??
=AVERAGE(ABS(Sheet1.B2),ABS(Sheet2.B2),ABS(Sheet
3.B2),ABS(Sheet4.B2),ABS(Sheet5.B2),ABS(Sheet6.B2))
Don't have quick access to see if its SUMIF would
support ranges. Have used spreadsheets going back to
visicalc on Apple, Lotus 1 on PC then thru version of
Lotus, Quattro Pro, and then Open and Libre Calc.
Wonder why the Sumif doesn't use similar base as sum?
Thanks for info.
> Inside one sheet there is no problem with abs or sumif using ranges.
>
> Kind regards,
> Regina
>
> --
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
++
Michael D. Setzer II - Computer Science Instructor
(Retired)
mailto:mi...@guam.net
mailto:msetze...@gmail.com
Guam - Where America's Day Begins
G4L Disk Imaging Project maintainer
http://sourceforge.net/projects/g4l/
++
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Function acting on range
Thanks very much for the help. I will flag using the AVERAGE and ABS and
can adequately gain a feel for the data I need with the STDEVP function.
I had recently been using some R style vector arithmetic and wondered if
similar could be achieved using ranges in LO.
Steve
On 17/03/2023 11:29, Regina Henschel wrote:
Hi Michael,
Michael D. Setzer II schrieb am 16.03.2023 um 22:45:
On 16 Mar 2023 at 18:22, ady wrote:
From:ady
Date sent:Thu, 16 Mar 2023 18:22:56 -0300
Subject:Re: [libreoffice-users] Function acting on
range
To:Users
Hi Regina,
But you can use {=AVERAGE({ABS($Sheet1.B1:$Sheet1.B30)} for example.
Besides the typo, for that to work it would need to be all in the same
worksheet, which is contrary to the initial setup.
ISTM that using SUM() and COUNT() would be simpler, if the data is
already with the layout as described, in multiple sheets.
Don't see how just using SUM() and COUNT() would
work.
Sum() and Count() work an ranges which include several sheets. So if
you did not need Abs(), then Sum()/Count() would give the average.
Therefore the problem here is, that Steve needs the Abs() function,
for what ever reason.
Unfortunately Sumif() does neither work on ranges over several sheets.
So the solution
(Sumif(myRange;">0")-Sumif(myRange;"<0")/Count(myRange) will not work,
if myRange goes over several sheets.
Inside one sheet there is no problem with abs or sumif using ranges.
Kind regards,
Regina
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Function acting on range
Hi Michael,
Michael D. Setzer II schrieb am 16.03.2023 um 22:45:
On 16 Mar 2023 at 18:22, ady wrote:
From:ady
Date sent:Thu, 16 Mar 2023 18:22:56 -0300
Subject:Re: [libreoffice-users] Function acting on
range
To:Users
Hi Regina,
But you can use {=AVERAGE({ABS($Sheet1.B1:$Sheet1.B30)} for example.
Besides the typo, for that to work it would need to be all in the same
worksheet, which is contrary to the initial setup.
ISTM that using SUM() and COUNT() would be simpler, if the data is
already with the layout as described, in multiple sheets.
Don't see how just using SUM() and COUNT() would
work.
Sum() and Count() work an ranges which include several sheets. So if you
did not need Abs(), then Sum()/Count() would give the average.
Therefore the problem here is, that Steve needs the Abs() function, for
what ever reason.
Unfortunately Sumif() does neither work on ranges over several sheets.
So the solution
(Sumif(myRange;">0")-Sumif(myRange;"<0")/Count(myRange) will not work,
if myRange goes over several sheets.
Inside one sheet there is no problem with abs or sumif using ranges.
Kind regards,
Regina
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Function acting on range
On 16 Mar 2023 at 18:22, ady wrote:
From:ady
Date sent:Thu, 16 Mar 2023 18:22:56 -0300
Subject:Re: [libreoffice-users] Function acting on
range
To:Users
> Hi Regina,
>
> > But you can use {=AVERAGE({ABS($Sheet1.B1:$Sheet1.B30)} for example.
>
> Besides the typo, for that to work it would need to be all in the same
> worksheet, which is contrary to the initial setup.
>
> ISTM that using SUM() and COUNT() would be simpler, if the data is
> already with the layout as described, in multiple sheets.
Don't see how just using SUM() and COUNT() would
work. Assuming the use of ABS was wanting to change
negative values to positive values a simple sum of
numbers would give a value different from the sum of
absolute values.
In my previous post I used number 1 2 3 -1 -2 -3 in
range, so a SUM of range would result in 0, so average
would be 0. SUM of the absolute value results in 12 and
then average would be 2.
Perhaps I'm missing something?
Did a test that seems to work.
Put number in B2 to G2
1 2 3 -1 -2 -3
=(SUMIF(B2:G2,">=0")-SUMIF(B2:G2,"<0"))/COUNT(B2:G2)
Adds all the non-negative numbers and subtracts sum of
negative numbers to get a total of all the abs values.
That should work if I understand it. Don't know if any
cell could be blank, so might need COUNTA or just
divide by number of cells.
Don't know why that is wanted, but seem the ABS in
function wants that??
>
> Regards,
> Ady.
>
> --
> To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
++
Michael D. Setzer II - Computer Science Instructor (Retired)
mailto:mi...@guam.net
mailto:msetze...@gmail.com
Guam - Where America's Day Begins
G4L Disk Imaging Project maintainer
http://sourceforge.net/projects/g4l/
++
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Function acting on range
I was able to accomplish array math by clicking on the fx button on the row
above the letters naming columns, then click the array checkbox in the
lower left of the function wizard dialog box.
After that the function listing shows {} around the function, but you can't
type that in to get the array checkbox turned on.
And the actual difference in the file is a couple opendocument XML
functions I'd never heard of "numberarray in x" and "number array in y" but
that's not how they are spelled.
[image: Screenshot from 2023-03-16 16-12-32.png]
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Function acting on range
Hi Regina,
> But you can use {=AVERAGE({ABS($Sheet1.B1:$Sheet1.B30)} for example.
Besides the typo, for that to work it would need to be all in the same
worksheet, which is contrary to the initial setup.
ISTM that using SUM() and COUNT() would be simpler, if the data is
already with the layout as described, in multiple sheets.
Regards,
Ady.
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Function acting on range
Two separate issues. A_ The curly brackets are not to be introduced manually. They are only a sign that the formula is not "normal" (as introduced by pressing [ENTER]) but instead is an array formula, introduced by simultaneously pressing [CTRL]+[SHIFT]+[ENTER] (CSE). Search the wiki or the web for info. B_ Not every function will accept 3D arguments (i.e. ranges from different worksheets), even when using array formulas. You can use ABS() as an array formula, but within the same worksheet. Currently, ABS() does not accept 3D arguments, IIUC. I could be wrong. So, you would have to use ABS() in each sheet (as normal or as CSE), and then you can use AVERAGE() as a 3D formula, whether with [ENTER] alone or with CSE, depending on the specific case. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Function acting on range
Hi Steve,
Steve Edmonds schrieb am 16.03.2023 um 21:37:
On 16/03/2023 23:12, Regina Henschel wrote:
Hi Steve,
Steve Edmonds schrieb am 16.03.2023 um 01:13:
Hi, on the off chance I will be pleasantly surprised, can a function
act on a range.
i.e can I do something like =AVERAGE(ABS($Sheet1.B28:$Sheet19.B28))
to average the absolute value of the numbers in the range.
The above seems to return only the last value of the range. I can
achieve this with an intermediate column on every sheet.
Enter it as array-function. That is using "Ctrl-Shift-Enter" instead
of "Enter" or mark the "Array" checkbox in the Function Wizard.
Kind regards,
Regina
Thanks.
Do you mean like {=AVERAGE(ABS($Sheet1.B30:$Sheet19.B30) )} as this
gives a 504 error and =AVERAGE({ABS($Sheet1.B30:$Sheet19.B30)} ) and
{=AVERAGE({ABS($Sheet1.B30:$Sheet19.B30)} )} both give a 539 error.
My fault. I now see, that you have a range over several sheets. That is
not possible with array-function.
But you can use {=AVERAGE({ABS($Sheet1.B1:$Sheet1.B30)} for example.
The problem is the ABS function. Otherwise a simple
=sum($Sheet1.B1:$Sheet4.B1)/count($Sheet1.B1:$Sheet4.B1)
would work.
Kind regards,
Regina
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Function acting on range
On 16/03/2023 23:12, Regina Henschel wrote:
Hi Steve,
Steve Edmonds schrieb am 16.03.2023 um 01:13:
Hi, on the off chance I will be pleasantly surprised, can a function
act on a range.
i.e can I do something like =AVERAGE(ABS($Sheet1.B28:$Sheet19.B28))
to average the absolute value of the numbers in the range.
The above seems to return only the last value of the range. I can
achieve this with an intermediate column on every sheet.
Enter it as array-function. That is using "Ctrl-Shift-Enter" instead
of "Enter" or mark the "Array" checkbox in the Function Wizard.
Kind regards,
Regina
Thanks.
Do you mean like {=AVERAGE(ABS($Sheet1.B30:$Sheet19.B30) )} as this
gives a 504 error and =AVERAGE({ABS($Sheet1.B30:$Sheet19.B30)} ) and
{=AVERAGE({ABS($Sheet1.B30:$Sheet19.B30)} )} both give a 539 error.
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Function acting on range
Hi Steve, Steve Edmonds schrieb am 16.03.2023 um 01:13: Hi, on the off chance I will be pleasantly surprised, can a function act on a range. i.e can I do something like =AVERAGE(ABS($Sheet1.B28:$Sheet19.B28)) to average the absolute value of the numbers in the range. The above seems to return only the last value of the range. I can achieve this with an intermediate column on every sheet. Enter it as array-function. That is using "Ctrl-Shift-Enter" instead of "Enter" or mark the "Array" checkbox in the Function Wizard. Kind regards, Regina -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Function acting on range
On 16 Mar 2023 at 13:13, Steve Edmonds wrote: Date sent: Thu, 16 Mar 2023 13:13:03 +1300 To: Users From: Steve Edmonds Subject:[libreoffice-users] Function acting on range > Hi, on the off chance I will be pleasantly surprised, can a function act > on a range. > > i.e can I do something like =AVERAGE(ABS($Sheet1.B28:$Sheet19.B28)) to > average the absolute value of the numbers in the range. > The above seems to return only the last value of the range. I can > achieve this with an intermediate column on every sheet. > Steve Believe ABS returns absolute value of just a number or cell, got errors when I tried a range. Did a test that seems to work. Put number in B2 to G2 1 2 3 -1 -2 -3 =(SUMIF(B2:G2,">=0")-SUMIF(B2:G2,"<0"))/COUNT(B2:G2) It sums up all the positive number and zeros, and then subtracts the sum of all the negative number) that is then divided by count. In this case sums were 6 and -6 resulting in 12, and then dividing by 6 result was 2. Think it would work using range across sheets? Don't know if any cells might contain blanks, so might just want to hard code number to divide? > > -- > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > Problems? > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > List archive: https://listarchives.libreoffice.org/global/users/ > Privacy Policy: https://www.documentfoundation.org/privacy ++ Michael D. Setzer II - Computer Science Instructor (Retired) mailto:mi...@guam.net mailto:msetze...@gmail.com Guam - Where America's Day Begins G4L Disk Imaging Project maintainer http://sourceforge.net/projects/g4l/ ++ -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] Function acting on range
Hi, on the off chance I will be pleasantly surprised, can a function act on a range. i.e can I do something like =AVERAGE(ABS($Sheet1.B28:$Sheet19.B28)) to average the absolute value of the numbers in the range. The above seems to return only the last value of the range. I can achieve this with an intermediate column on every sheet. Steve -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
