On 16 Mar 2023 at 23:29, Regina Henschel wrote: Subject: Re: [libreoffice-users] Function acting on range To: [email protected] From: Regina Henschel <[email protected]> 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 <[email protected]> > > Date sent:Thu, 16 Mar 2023 18:22:56 -0300 > > Subject:Re: [libreoffice-users] Function acting on > > range > > To:Users <[email protected]> > > > > > >> 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: [email protected] > 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:[email protected] mailto:[email protected] Guam - Where America's Day Begins G4L Disk Imaging Project maintainer http://sourceforge.net/projects/g4l/ +------------------------------------------------------------+ -- To unsubscribe e-mail to: [email protected] 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
