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+...@lang.hm
To: pete nikolic pg.nikol...@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.nikol...@gmail.com
To: James E Lang jim+...@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+...@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+unsubscr...@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+unsubscr...@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
