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 <users@global.libreoffice.org>
From:                   Steve Edmonds <steve.edmonds@ptglobal.com>
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+unsubscribe@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:mikes@guam.net                            
 mailto:msetzerii@gmail.com
 Guam - Where America's Day Begins                        
 G4L Disk Imaging Project maintainer 
 http://sourceforge.net/projects/g4l/
+------------------------------------------------------------+




-- 
To unsubscribe e-mail to: users+unsubscribe@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