Projections Formula

[Guest]

I have a multiple sheet file that I use to develop projections from a
forecast + actual YTD. Each month's cell analyzes whether there is anything
in the actual sheet, if not, it looks for something in the forecast sheet, if
there is nothing there is sums the year to date actual and subtracts that
from year end budget and then divides that by how many months are left to see
what I need to do the rest of the year to meet budget.

The formula is =IF(Actual!J21>0,Actual!J21,IF('Forecast
Input'!J21>0,'Forecast Input'!J21,((($O21-SUM($B21:$H21))-SUM('Budget
2007'!$I21:$M21))*(1/5))+'Budget 2007'!J21))

My problem is that I am manually changing the formula every month to adjust
the range being summed and the number of months (i.e. 1/5). There could be
11 possibilites depending on month. I think there is probably a simple
solution that would avoid the updates but I've overthought it to the point
I've got myself confused.

Thanks,
Steve

 

[Guest]

try something like (1/(13-month(now())