Rolling Average for Prediction

[DangerMouse]

Hi all,

I'm hoping and anticipating that theres a really simple solution t
this question but I can't seem to get my head around it. Any advice yo
could provide would be much appreciated.

I'm simply calculating hit rate, or average on a monthly basis, and
want this data to inform future months in a predictive fashion.

Thus I have 10 in Jan, 5 in Feb, my avarage is obviously 7.5, thus
would like all months after Feb to predict the "hit" figure to be 7.5


I would like this to work on a rolling basis as evidently as the actua
figures are available the average changes and thus so does th
prediction.

I appreciate this isnt the most scientific way to model things but an
assistance would be appreciated.

Thn

 

[Mallycat]

In Column A1:A12 enter Jan, Feb, Mar etc.
In Column B1:B12 enter your actual results say 10, 7 etc
In cell B13, enter =AVERAGE(B1:B12)

The answer will change as you add new data points for the future
months

Matt

 

[Guest]

Assuming data s shown below in columns A to C then in C2 put:

=IF(B2<>"",B2,SUM($B$2:B2)/COUNTA($B$2:$B$16))

and copy down



Actual Prediction
Jan 10 10.00
Feb 5 5.00
Mar 9 9.00
April 8.00
Apr 8.00
May 8.00
Jun 8.00
May 8.00
Jul 8.00
Aug 8.00
Sep 8.00
June 8.00
Oct 8.00
Nov 8.00
Dec 8.00

HTH

 

[Bob Phillips]

=AVERAGE($A$1:A2)

and just copy down

Fixing A1 with $ means that the average grows its list as the data grows.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"DangerMouse" <[email protected]>
wrote in message
news[email protected]...

 

[Guest]

If you use the formula =average(a1:aX) where X is the cell immediately above
then each month you could replace the formula with the actual and the future
months will show the correct average.

 

[Guest]

Dooh!

=IF(B2<>"",B2,AVERAGE($B$2:B2))

Assuming data s shown below in columns A to C then in C2 put:

=IF(B2<>"",B2,SUM($B$2:B2)/COUNTA($B$2:$B$16))

and copy down



Actual Prediction
Jan 10 10.00
Feb 5 5.00
Mar 9 9.00
April 8.00
Apr 8.00
May 8.00
Jun 8.00
May 8.00
Jul 8.00
Aug 8.00
Sep 8.00
June 8.00
Oct 8.00
Nov 8.00
Dec 8.00

HTH

 

[DangerMouse]

Thanks Matt, thats great.

I still seem to have one slight problem though, as my "actual" figures
are pulled from another spreadsheet it seems to be putting future
months in as 0.00 rather than leaving the cell null. This obviously
knocks out my average, is there anyway around this problem?

Thanks again

 

[DangerMouse]

Hello again,

Well I've just figured out why the values display as 0... and read
about appropriate error trapping etc. However, I've just realised that
this is not an option as the result im linking to from another sheet is
actually 0! - as its the result of an addition of null cells.

The spreadsheet is too large to introduce error trapping to all the
Sum() functions, does anyone know of another way around this problem?

Untidy but effective is fine lol.

Cheers