2015 Archives by date, by thread · List index

# 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
```

### Context

Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.