Formulas for averages automatically updated when new column inserted

[Kimber]

I have a data for several products (rows)for each month (columns.) At the far right, I have the following columns (formula in parentheses): TOTAL (=SUM(M3:V3), 12 PERIOD AVERAGE (=X3/12), 6 PERIOD AVERAGE(=AVERAGE(R3:W3)), AND 3 PERIOD AVERAGE (=AVERAGE(U3:W3)). The worksheet is cumulative, but the columns with formulas only refer to the last 12, 6, and 3 months. Each month I insert a new column before the TOTAL column. Then I have to manual update/edit the 6 and 3 period average formulas to calculate based on the appropriate new range. Is there a way to have this occur automatically?


I appreciate your suggestions. Thank you.

 

[Luke M]

Sure thing, you just need to use a dynamic named range. Jon Peltier describes how to do this with the final intent of using it in a chart, but you could certainly plug the range name into your formula rather than a chart.

http://peltiertech.com/Excel/Charts/DynamicLast12.html


or Chandoo's article here:

http://chandoo.org/wp/2010/04/06/rolling-months/

 

[Kimber]

Thanks, Luke. I thought perhaps I should use OFFSET which has worked for me in worksheets where my data was in columns. I tried to use OFFSET for rows, but I couldn't get it to work properly. I will attempt to set up the dynamic named range as explained and hopefully I will be successful!

 

[Montrey]

offset will work for rows. just look at the offset syntax.

 

[satishkulkarni]

Hi, Kimber

my solution to your problem is like this (may be better solution available)

column a b and c are for average 3 months, average 6 months & average 12 months.

from column d to "n" number of columns (i have taken up to column y) are for months which every month you add one month.

put following formulas in every row of each product in column a b and c respectively.


For avg last 3 months (column a)


=AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN(D2)+MAX(0,COUNT(D2:Y2)-3))):INDIRECT(ADDRESS(ROW(),COLUMN(D2)+COUNT(D2:Y2)-1)))


for avg last 6 months (column b)

=AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN(D2)+MAX(0,COUNT(D2:Y2)-6))):INDIRECT(ADDRESS(ROW(),COLUMN(D2)+COUNT(D2:Y2)-1)))


for avg last 12 months (column c)


=AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN(D2)+MAX(0,COUNT(D2:Y2)-12))):INDIRECT(ADDRESS(ROW(),COLUMN(D2)+COUNT(D2:Y2)-1)))

 

[Faseeh]

Hi Kimber,


Kindly see this work out, might it help you:


http://dl.dropbox.com/u/60644346/Dynamic%20Average.xlsx


Regards,

Faseeh