Re: [libreoffice-users] Function acting on range

On 17/03/2023 13:08, Michael D. Setzer II wrote:
> 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??
Actually 29 sheets. I couldn't find a way to address sheets by position 
rather than name. The first sheet is 1, the last 19 and the ones in the 
middle have various names.
> =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