New to board...How to select # of cells immediately prior to

[kac918]

I have an spread sheet that contains sales information in columns representing monthly sales. The manager would like the last four months to be averaged into a fifth field. On the fifth month, she inserts a new column between last month and the 4 month average. She wants the average to automatically recalculate the average of the new month plus the prior three months. How can this be done?
ie. a1=January, b1=February, c1=March, d1=April, e1=Average sales, she inserts May into e1 and average sales becomes f1. She then wants f1 to automatically update using b1 through e1..and so on each month.
Is this possible? I am a novice on this board and writing this type of formula. Any help is greatly appreciated. I do not know how to write macros either.
THANKS for any input!

 

[Legacy 98055]

HI
Just change the formula in F1
=AVERAGE(A1:D1)
To
=AVERAGE(A1:E1)
Copy it
Paste it down the column
Tom

 

[kac918]

Thanks, but the manager does not want anyone to have to change the formula...Currently, if you add in a column between D1 and E1, the formula does change to =average(a1:e1)(5 months), but I need to know if there is a way that when the new column is added in, the formula would automatically change to =average(b1:e1)(4 months), and the following month change to =average(c1:f1). Basically, I think I have to link the range (4 cells prior to the average) to the formula, not the absolute cell name. Does that make more sense? Thank you for any input given.

 

[daleyman]

okay, had to play a little but this does work....

In this example there are values in cells a1 to d1 that need to be averaged.

This equation for the sum is in cell e1, and will move to f1 when the column is inserted...

=AVERAGE(INDIRECT("A1:" & ADDRESS(1,COLUMN(E1)-1)))

Try it and see!

In the equation A1 refers to start of range, 1 refers to the row number that this is all happening in, and column(E1)-1 returns the column number for the cell just to the left of the equation cell.

Good Luck!

 

[Mark W.]

On 2002-04-03 11:14, kac918 wrote:
Thanks, but the manager does not want anyone to have to change the formula...Currently, if you add in a column between D1 and E1, the formula does change to =average(a1:e1)(5 months), but I need to know if there is a way that when the new column is added in, the formula would automatically change to =average(b1:e1)(4 months), and the following month change to =average(c1:f1). Basically, I think I have to link the range (4 cells prior to the average) to the formula, not the absolute cell name. Does that make more sense? Thank you for any input given.

Enter the formula...

=AVERAGE(OFFSET(F1,,-4,,4))

...into cell F1.