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

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


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=553165

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

=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"
wrote in message
...

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

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

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

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
------------------------------------------------------------------------
DangerMouse's Profile: http://www.excelforum.com/member.php...o&userid=27755
View this thread: http://www.excelforum.com/showthread...hreadid=553165

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


--
DangerMouse
------------------------------------------------------------------------
DangerMouse's Profile: http://www.excelforum.com/member.php...o&userid=27755
View this thread: http://www.excelforum.com/showthread...hreadid=553165