Sum of column by automatically choosing range of 1 year

[[email protected]]

Hi
I've a excel sheet in which first row consist of month names and rest of the rows contains numerical values. I've to update this every month by inserting a new column for next month.
Now in last column i want put a summation formulae. I have to calculate sum of 1 year(for example if i'm adding a column say May-13,then i want sum of May-12 to May-13).Now to calculate this i've to everytime change the sum range manually.I want the range to shift and be limited to 1 year starting from the last newly inserted month.Can this be done by soome formulae or macro.Pls help

I'm attaching sheet for reference.


Regards

Pavan Raj

[martindwilson]

Try
=((index(3:3,match(max($2:$2),$a$2:index($2:$2,count($2:$2)+2),0))-index(3:3,match(max($2:$2),$a$2:index($2:$2,count($2:$2)+2),0)-12))/100)/index(3:3,match(max($2:$2),$a$2:index($2:$2,count($2:$2)+2),0)-12) in yoy%

[AZ-XL]

Hi,

This is another alternative, Paste the formula below "Sales" column and the drop down
=SUM(INDIRECT(ADDRESS(ROW(),COLUMN($BE$2)-12)&":"&ADDRESS(ROW(),COLUMN($BE$2)-1)))

[[email protected]]

Try
=((index(3:3,match(max($2:$2),$a$2:index($2:$2,count($2:$2)+2),0))-index(3:3,match(max($2:$2),$a$2:index($2:$2,count($2:$2)+2),0)-12))/100)/index(3:3,match(max($2:$2),$a$2:index($2:$2,count($2:$2)+2),0)-12) in yoy%

thanks but its not working .If u can show it in the excel sheet i've attached then it will be helpful..

[[email protected]]

Hi,

This is another alternative, Paste the formula below "Sales" column and the drop down
=SUM(INDIRECT(ADDRESS(ROW(),COLUMN($BE$2)-12)&":"&ADDRESS(ROW(),COLUMN($BE$2)-1)))

thanks but when iserting a new coloumn ...its not working

[martindwilson]

oops i put / instead of * it should be

Please Login or Register  to view this content.

and MoM
would be

Please Login or Register  to view this content.

[[email protected]]

thanks alot ...its working :-)