Date: prev next · Thread: first prev next last
2015 Archives by date, by thread · List index


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.

-- 
Jim

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

On Sat, 25 Apr 2015 01:30:53 -0700
James E Lang <jim+lou@lang.hm> wrote:

Pete,

Pardon me. I'm confused. 

When you say C5 (for example) which do you mean, Row 5 in Column C or the fifth
column?

Where is the calculation?

You speak of C1, C3, C5, C7, ... but what about C2, C4, C6, C8, ...?

Here is what I understand (my suppositions): 

Row 1 is a ser of column headings. 

Column A contains a set of names (of regions maybe). 

Columns C, E, G, I, ... are headed by dates (e.g. Jan 1, Feb 14, etc.). 

At the intersection of column C and row 2 is a number (maybe a counter of some
kind) relating to the first region from Jan 1 through Feb 13. Other cells in
columns C, E, G, I, ... get filled similarly.

Starting with the third time interval you want to calculate the average of the
most recent three vakues stored in those cilumns bur adjusted by some mystery
constant for that region (column B maybe) and maybe store that result in columns
D, F, H, J ....

If I'm right,  which is a mighty big IF, then the task is quite easily
accomplished but I'll await your response to my suppositions before going any
further. Besides that I need some sleep.




Hi right   ok lets see 


take an ever increasing  list of values  for the first 2 values do nothing  when a
third value gets added add the three together  then add anoter value to the list
ignore the first 2 values add the next three  add another value  then ignore first 3
values  add the remaining three  but every time a value is added discard the
previous sum of three  ...



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.