Re: [libreoffice-users] Function acting on range

On 16 Mar 2023 at 18:22, ady wrote:


From:ady <adylo811517@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. 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+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