Re: [libreoffice-users] Function acting on range

On 16 Mar 2023 at 23:29, Regina Henschel wrote:

Subject:                Re: [libreoffice-users] Function acting on 
range
To:                     users@global.libreoffice.org
From:                   Regina Henschel 
<rb.henschel@t-online.de>
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 <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.
>
> 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: 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