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