On 16 Mar 2023 at 13:13, Steve Edmonds wrote: Date sent: Thu, 16 Mar 2023 13:13:03 +1300 To: Users <[email protected]> From: Steve Edmonds <[email protected]> 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: [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
