Re: [libreoffice-users] Function acting on range

Thanks very much for the help. I will flag using the AVERAGE and ABS and 
can adequately gain a feel for the data I need with the STDEVP function.
I had recently been using some R style vector arithmetic and wondered if 
similar could be achieved using ranges in LO.
Steve

On 17/03/2023 11:29, Regina Henschel wrote:
> 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.
>
> 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