selecting cells in calc sheet

users
#1

Hi Folks ..

I need to be able to select the last 3 in a series of cells that change on a weekly
basis

so i week 1 there would be 1 cell no action
week 2 2 cells no action
week 3 3 cell sum three cells
week 4 4 cells sum last three
week 5 5 cells sum last three

and so it goes on for 8 weeks always just summing the last 3 cells in the
series then divide by 3 subtract 30 = result ..

Pete

#2

pete nikolic wrote:

>
> Hi Folks ..
>
>
> I need to be able to select the last 3 in a series of cells that change on a weekly
> basis
>
>
>
> so i week 1 there would be 1 cell no action
> week 2 2 cells no action
> week 3 3 cell sum three cells
> week 4 4 cells sum last three
> week 5 5 cells sum last three
>
> and so it goes on for 8 weeks always just summing the last 3 cells in the
> series then divide by 3 subtract 30 = result ..

That is rather vague. Where is the week number? How is the series represented? What do you want to do with the result?

#3

I'm guessing that you want separate results for each week, not just a single result - so that in week 5, for example, you would have three results, not one. Let's imagine your data is in column A, starting in A1. In B3, insert:
=AVERAGE(A1:A3)-30
and fill down the column.

If you want to suppress the display in B cells for which there is not yet an A-cell vale, try something like:
=IF(A3=0;"";AVERAGE(A1:A3)-30)

I trust this helps.

Brian Barker

#4

Hi Brian

ok lets see ..

  if we start as will be the case C3 will contain say 50 (the actual dates are
  of little importance ) a month later C5 will be say 47 and so on every month
  the next cell would be C7 then C9 then C11 what i need is to be able to
  automatically pick the last 3 cells containing data so when C9 is filled in then
  it needs to automatically choose C9 C7 C5 and add them then divide by 3 then
  subtract a variable defined on the day ..

Hope this makes it a little clearer

Thanks Pete .

#5

pete nikolic wrote:

> if we start as will be the case C3 will contain say 50 (the actual dates are
> of little importance ) a month later C5 will be say 47 and so on every month
> the next cell would be C7 then C9 then C11 what i need is to be able to
> automatically pick the last 3 cells containing data so when C9 is filled in then
> it needs to automatically choose C9 C7 C5 and add them then divide by 3 then
> subtract a variable defined on the day ..
>
> Hope this makes it a little clearer
>
That would then be:

IF(ISBLANK(C7);"";AVERAGE(C3;C5;C7)-30)

#6

Pete,

You still leave a lot unsaid such as whether the result is in a single fixed location, what if anything is in the even numbered rows of column C, whether any other column can be used for identification purposes, what if anything is below the most recent month's data, etc. You imply that there's no more than eight months worth of data so I guess C17 ends the series.

Without some of the information vacuum filled I'm not sure what we can do for you.

#7

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.

#8

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.

#9

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.

#10

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.

#11

Correction. I ran across an old message from Mr Barker that suggested using the OFFSET() function for something.

#12

For anyone who might have taken an interest in this discussion, I omitted a right parenthesis when I first posted this formula. It is now correct.

#13

I find OFFSET() quite useful whenever I build a table with a running balance (for instance where the balance in X10 = X9+W10). Without the OFFSET() function, if I needed to insert a row between rows 9 & 10, the new row would not be included in the running balance unless I edited the formula. But if the original X10's formula is "=OFFSET(X10,-1,0)+W10" then the new row is automatically included in the running balance.

-- Tim