Projections Formula
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!J210,Actual!J21,IF('Forecast Input'!J210,'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 |
Projections Formula
try something like (1/(13-month(now())
"SWM" wrote: 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!J210,Actual!J21,IF('Forecast Input'!J210,'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 |
All times are GMT +1. The time now is 12:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com