# Thread: Predicting what the next numbers will be in a sequence?

1. ## Predicting what the next numbers will be in a sequence?

I have a list of numbers in cells in a row. Is the an Excel function which can predict what the next number is likely to be - so some sort of trend analysis?

Alan

2. I think the "Forecast" formula is the best I am going to get. It still gives weird results occasionally but I can't find anything better.

Alan

3. forecast, trend for linear growth (assumes that y=mx + b), for curves, growth function for exponential growth (y = b*m^x) For other growth pattern you would need more complex and would depend on the anticipated growth pattern expected. There is no one-answer solution to the best method. You won't know the best method of those tried until you have an actual future number to compare it to.

Steve

4. Cheers - that's the conclusion I had come to as well.

I'm still playing

5. If you provide some data, perhaps we can offer more specific insight. In my job, I do a little data analysis and model creation for prediction. It is best to interpolate within your "design space" but the same mechanics apply to extrapolation (you just have much less confidence in the values).

Steve

6. I only have 6 points for each of the forecasts. Most of the data looks OK, but some is just silly (but only because I know better). For example:
196 196 39 39 39 39

This gives a negative number for the next value. What it should be is "39" but only because I know.

19 19 19 19 19 13

The forecast gives 15 as the next one. But the trend is down - so why bring it back up again?

What would be nice is if it could give more weighting to later numbers in the sequence.

My formula is =FORECAST(7,Dxx:Ixx,D96:I96) where row 96 contains the numbers 1,2,3,4,5,6 and the Dxx and Ixx are the current rows.

Alan

7. Or you can write a macro to follow this technique of building a formula from a set of values then using the formula to calculate the next in the sequence

http://www.johansens.us/sane/education/formula.htm

8. The values you get (-35) and 15 make sense to me. They are based on least-squares linear regression [minimizing the square of the deviation in the Y-values [ie picking a line that minimizes the sum of (Ycalc-Yobs)²].

But it boils down to what you choose to use to forecast to get the Ycalc. Based on some of the trendlines you use in Excel [See the attached file]. , the first dataset could range from about -35 to 70 and the 2nd set from 10 to 16.5.

You can even just use the overall average value as a future prediction which gives 91 and 18 for the 2 sets.

If you want to weigh the values by the points, you can calculate the trendlines for 2, 3, 4, 5, and 6 (D99:I99 for set1 and N99:R99 for set 2). Once you have the 5 values you can average them as is (which will weigh the later points more since they are used more often) data set 1 is about 12 and data set 2 is about 15. You could also weigh those values (I used a weighting of 1,5,10,100,1000, respectively as an example in D95:I95) and calculate those values.

It all depends on what works best for you.

Steve

9. ## Finding a formula for a set of numbers

Although you are looking for a spreadsheet formula, this may help you validate the accuracy of the results of the formula you compile.

I followed through with the technique from the link I posted and came up with the formula that will calculate the next number in the sequence. I verified this with a formula generator on the authors site and tested it with the values you had posted. The formula generates each value correctly and can predict any value for any slot in the sequence:

y = (157/30 * x^5) - (785/8 * x^4) + (8321/12 * x^3) - (18055/8 * x^2) + (64527/20 * x) - 1374 where x equals the number of the sequence slot

Sequence.png

10. With 6 points a 5th degree polynomial will exactly go through all the points (just like a straight line can go exactly through 2 points). But the trends can be pretty odd. (Excel does this with a trend line (polynomial of order = 5). Set 1 predicts 824 (as you note, but the OP suggested it should be around 39). Set 2 is abut -17...

Steve

11. Here is a workbook that shows how you can get the polynomial values directly from excel using LINEST function.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•