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

kac918 · Apr 3rd, 2002, 11:20 AM

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!

Tom Schreiner · Apr 3rd, 2002, 11:41 AM

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

kac918 · Apr 3rd, 2002, 12:14 PM

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 · Apr 3rd, 2002, 01:05 PM

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. · Apr 3rd, 2002, 01:11 PM

Quote:

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 3rd, 2002, 11:20 AM	#1
kac918 New Member Join Date: Apr 2002 Posts: 2	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!

Apr 3rd, 2002, 11:41 AM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	HI Just change the formula in F1 =AVERAGE(A1:D1) To =AVERAGE(A1:E1) Copy it Paste it down the column Tom

Apr 3rd, 2002, 12:14 PM	#3
kac918 New Member Join Date: Apr 2002 Posts: 2	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.

Apr 3rd, 2002, 01:05 PM	#4
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	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!