Forecasting Data

[BMil8]

Hello!,

I can't seem to figure out how to forecast/trend my data. What I have is 9 months listed in row 1 beginning at A1. Beginning in row 2 I list the number of new customers we had in each month. I need to forecast what the next 3 months will look like. I tried using the TREND formula but it doesn't seem to be giving me correct results.

Can anybody provide me with a formula that can help me forecast the next 3 values?

Thank you and please let me know if you need any more information.

Brian

 

[ZPhantom]

try the forecast funtion

= forecast($A10, $B$1:$B$9, $A$1:$A$9)

more info:
http://support.microsoft.com/kb/828236


ZP

 

[BMil8]

Thanks ZP. Do you know of a way to make this formula work if the values are not adjacent to one another? Meaning if I have Month 1 in cell A1, month 2 in cell C1, month 3 in cell E1, etc.

It seems I can only get the formula to work if they are right next to each other.

 

[ZPhantom]

just change $A$1:$A$9 to wherever the months run (probably $A$1:$I$1)
and the $B$1:$B$9 to where the data is (at a guess $A$2:$I$2)
finally change $A10 to wherever the month you want to predict is.



the more data you can give it the better the accuracy - but dont include the forecasted values in your "input" information

ZP

 

[BMil8]

The problem is that I can't use A2:I2 because the data is not next to each other. A2 has month 1 data, but B2 is another unrelated field, and then C2 has month 2 data.

What's the reason for not using forecasted data in the formula?

 

[ZPhantom]

Ah sorry I misread, (forgot how the alphabet works for a second there)

im not aware of an easy way to use data thats split up (anybody?)

The reason for not using forcasted data comes down to information integrity:

Each forecast is the computers "best guess" at the result, if you start telling it that this information is actually accurate then you'll end up with a very large margin of error.

ZP

 

[BMil8]

The alphabet is tricky. I'm also a member of the Mr. Alphabet message board.

Thanks for your help. I just ordered my data in a way that allows the forumla to work.

Thanks again,
Brian