Rolling Calculation (2007)

Topic

New Reply

I have a forecast that I am trying to sum individual monthly totals. The forecast has numbers in each month Jan – Dec. I have a cell that represents YTD forecast numbers that I am having to change each month to pick up the next forecasted months value.

Example- Its November, my YTD forecast number would be the sum of Jan-Nov. Next month, I would like the formula to automatically recognize that it is now December and extend the range of the formula to pick up Jan-Dec numbers.

Right now, I go into the first cell of the table, extend/change the formula, and then paste it down the page…. THERE HAS GOT TO BE A BETTER WAY!

I have included a small portion of the file. See cell C7 for the current formula.

As always, your help and input is greatly appreciated.

Reply | Quote

Replies

In the attached version, I entered 01/01/08 in G6, 02/01/08 in H6, then selected G6:H6 and filled right to R6.
Next, I changed the number format to the custom format mmm so that only the abbreviated month name is displayed.
I changed the formula in C7 to

=SUMIF($G$6:$R$6,"<="&$F$1,G7:R7)


and filled down to C9.
When you change the date in H1, the results will be updated automatically.
If you always want to refer to the current month, enter the formula =TODAY() in F1.

Reply | Quote