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. 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: [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
