Re: [libreoffice-users] selecting cells in calc sheet

Correction. I ran across an old message from Mr Barker that suggested using the OFFSET() function 
for something.

-- 
Jim

-----Original Message-----
From: James E Lang <jim+lou@lang.hm>
To: pete nikolic <pg.nikolic1@gmail.com>, LibreOffice Users <users@global.libreoffice.org>
Sent: Sat, 25 Apr 2015 15:03
Subject: Re: [libreoffice-users] selecting cells in calc sheet

You're in business. The AVERAGE() function does the same thing as the SUM() function divided by the 
COUNT() function. I would use AVERAGE() as it is cleaner (more streamlined).

I'm glad I could help. I use the OFFSET() function myself to dynamically define ranges of cells. If 
somebody knows a better technique I hope they will chime in. I've never seen anyone else post a 
practical use for this function but for me, any spreadsheet program that lacks support for it fails 
to gain my acceptance.

-- 
Jim

-----Original Message-----
From: pete nikolic <pg.nikolic1@gmail.com>
To: James E Lang <jim+lou@lang.hm>
Sent: Sat, 25 Apr 2015 12:17
Subject: Re: [libreoffice-users] selecting cells in calc sheet

On Sat, 25 Apr 2015 11:46:23 -0700
James E Lang <jim+lou@lang.hm> wrote:

> The main thing I'm not understanding is whether the formula is to be one fixed
> place or if after 3 data points it is in cell X but after 4 data points it is in
> cell Y. The solution I gave you applies to the latter case.
>
> For the former case I would use a more complex formula like this using your latest
> specification.
>
> =IF(COUNT(A1:A50)>2,AVERAGE(OFFSET(A1,0,COUNT(A1:A50)-3,1,3)-30,"")
>
> That says, if there are more than two values then define a horizontal range
> composed of the last three filled cells, average that range, and subtract 30. This
> works as long as they are no empty or text cells before the values of interest in
> A1:A50. This process gets a little bit (to say the least) more hairy if there are
> intervening cells.

Hi Jim ..


Right this is looking promissing   the actual location of the formula /result will
be in a separate cell on the same row but outside the range of entered values it
will also be copied to the next 30 or so rows below and edited to suit where
needed   . one other thing you have it averaging the cells they need to be added
together then divided by 3 unless the average function does the same thing 



Thanks      

Pete .
  

-- 
Illegitimi non carborundum . ro for the purists out there
Noli nothis permittere te terere. 

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted