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
<rb.hensc...@t-online.de>
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 <adylo811...@gmail.com>
Date sent:Thu, 16 Mar 2023 18:22:56 -0300
Subject:Re: [libreoffice-users] Function acting on
range
To:Users <users@global.libreoffice.org>
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