forecasting an average in excel 3

[jillseurre]

I want to be able to calculate some numbers based on a forecast average and actuals.

So for example I know the average forecast for calendar year 2004 will be 1.17.
Then on month 1 I get an actual number say 1.24, I then want to be able to put a forecast for the next 11 months to achive a 1.17 average.

Then on month 2 I get and actual number say 1.09.
So MTH1 = 1.17 MTH2 = 1.09 now calculate the forecast average for the next 10mths to achive 1.17

Then on month 3 I get and actual number say 1.35.
So MTH1 = 1.17 MTH2 = 1.09 MTH3 = 1.35 now calculate the forecast average for the next 9mths to achive 1.17

I would like to have this automatically updated

any ideas

 

[bluedragon2]

Try this:

In A1 through A12 hold your monthly hard values. If you do not have one for that month leave it blank.

In B1, put:

=(14.04-SUM(A1:A12))/COUNTBLANK(A1:A12)

Now if A1:A12 are blank, B1 will be 1.17. When you start adding your hard tables, it will adjust to show what value is needed per month to get your average goal.



Blue

If I wasn't Blue, I would just be a Dragon...

 

[Zathras]

If your data will be going into cells A2 thru L2 then you can set up formulas this way and replace the formulas with actuals as they become known:
[blue]

A2: 1.24
B2: =(12*$M$2-SUM($A2:A2))/(13-COLUMN(B1))
       (copy formula from B2 into C2:L2)
M2: 1.17

[/color]
If you need this in some other columns, you will need to adjust the fixed column/row references to get the formulas you want.

 

[xlhelp]

Well same as Zathras, but since I did the work might as well post it.

If your montly values are in A2, A3...etc

then

=(1.17*12-SUM($A$2:A2))/(13-ROW(A2))
and copy the formula down

Deja Moo - "I have heard that bull before"

 

[jillseurre]

Thats great Thanks for all your help & Support, now working OK...
I now have a graph question which I will pose seperately