Rolling Average for Prediction

[DangerMouse]

Hi all,

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

I'm simply calculating hit rate, or average on a monthly basis, and I 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 I 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 actual figures are available the average changes and thus so does the prediction.

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

Thnx

[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

[Toppers]

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" wrote:

>
> Hi all,
>
> I'm hoping and anticipating that theres a really simple solution to
> this question but I can't seem to get my head around it. Any advice you
> could provide would be much appreciated.
>
> I'm simply calculating hit rate, or average on a monthly basis, and I
> 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 I
> 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 actual
> figures are available the average changes and thus so does the
> prediction.
>
> I appreciate this isnt the most scientific way to model things but any
> assistance would be appreciated.
>
> Thnx
>
>
> --
> DangerMouse
> ------------------------------------------------------------------------
> DangerMouse's Profile: http://www.excelforum.com/member.php...o&userid=27755
> View this thread: http://www.excelforum.com/showthread...hreadid=553165
>
>

[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]...
>
> Hi all,
>
> I'm hoping and anticipating that theres a really simple solution to
> this question but I can't seem to get my head around it. Any advice you
> could provide would be much appreciated.
>
> I'm simply calculating hit rate, or average on a monthly basis, and I
> 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 I
> 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 actual
> figures are available the average changes and thus so does the
> prediction.
>
> I appreciate this isnt the most scientific way to model things but any
> assistance would be appreciated.
>
> Thnx
>
>
> --
> DangerMouse
> ------------------------------------------------------------------------
> DangerMouse's Profile:
http://www.excelforum.com/member.php...o&userid=27755
> View this thread: http://www.excelforum.com/showthread...hreadid=553165
>

[Michael]

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.

"DangerMouse" wrote:

>
> Hi all,
>
> I'm hoping and anticipating that theres a really simple solution to
> this question but I can't seem to get my head around it. Any advice you
> could provide would be much appreciated.
>
> I'm simply calculating hit rate, or average on a monthly basis, and I
> 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 I
> 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 actual
> figures are available the average changes and thus so does the
> prediction.
>
> I appreciate this isnt the most scientific way to model things but any
> assistance would be appreciated.
>
> Thnx
>
>
> --
> DangerMouse
> ------------------------------------------------------------------------
> DangerMouse's Profile: http://www.excelforum.com/member.php...o&userid=27755
> View this thread: http://www.excelforum.com/showthread...hreadid=553165
>
>

[Toppers]

Dooh!

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

"Toppers" wrote:

> 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" wrote:
>
> >
> > Hi all,
> >
> > I'm hoping and anticipating that theres a really simple solution to
> > this question but I can't seem to get my head around it. Any advice you
> > could provide would be much appreciated.
> >
> > I'm simply calculating hit rate, or average on a monthly basis, and I
> > 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 I
> > 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 actual
> > figures are available the average changes and thus so does the
> > prediction.
> >
> > I appreciate this isnt the most scientific way to model things but any
> > assistance would be appreciated.
> >
> > Thnx
> >
> >
> > --
> > DangerMouse
> > ------------------------------------------------------------------------
> > DangerMouse's Profile: http://www.excelforum.com/member.php...o&userid=27755
> > View this thread: http://www.excelforum.com/showthread...hreadid=553165
> >
> >

[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

Wow lots more replies by the time I wrote this...Thanks! The IF formula looks useful and could solve the problem listed above, but the data could technically be "0" so this isnt an ideal solution.

[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