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.
--
Jim
-----Original Message-----
From: pete nikolic <pg.nikolic1@gmail.com>
To: James E Lang <jim+lou@lang.hm>, users@global.libreoffice.org
Sent: Sat, 25 Apr 2015 11:11
Subject: Re: [libreoffice-users] selecting cells in calc sheet
On Sat, 25 Apr 2015 10:53:38 -0700
James E Lang <jim+lou@lang.hm> wrote:
You did not correct any of my suppositions so here is my very detailed
recommendation. Note that my quotation marks surrounding text are NOT to be keyed
into the spreadsheet. For the time being I will limit my discussion to columns A
through L and rows 1 through 5.
Starting from scratch with a new (empty) spreadsheet, select cell A1 and enter
"Region"
In B1 enter "Constant"
In cell C1 enter the first date such as "01/01/2015"
Leave D1 empty but merge C1 and D1.
Continue with E1 & F1, G1 & H1, I1 & J1, and K1 & L1 in the same manner.
Now go to the data rows.
In cell A2 enter the name of the first region and in B2 that region's constant
such as "30"
Prepare cells A3 through B5 similarly.
Leave cells C2 through G2 empty for now.
The long sought formula first appears in cell H2. It is
=IF(G2="","",AVERAGE(C2,E2,G2)-$B2)
Now select cells G2 through H2 and copy (Ctrl+C).
Finally select cells G2 through L5 and paste (Ctrl+V). Of course this final step
can be modified to cover up to 511 date ranges and over one million regions.
In production you might accidentally enter data where the formula is. Remember
that undo (Ctrl+Z) is your friend when this happens. You can also use copy and
paste or fill vertically to recover.
I hope this satisfies your requirements, If not then you'll have to explain your
requirements better.
Hi James .
It may in a round about fashion lets just totally forget about dates .
Lets imagine a row that has number entered in cells on that row at intervals (the
interval is totally unimportant) i enter a number in A1 then another in A2 with
just input in A1 & A2 no action enter a number in A3 then sum the numbers (easy so
far) the enter a number in A4 then i want it to forget the value in A1 and sum A2
A3 &A4 then enter a number in A5 i then want it to forget A1 & A2 and Sum A3 A4 &A5
and so on does this help any
PS thanks so far this is one aspect of spread sheets that i find confusing
to say the least
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.