Results 1 to 11 of 11

20140402, 02:50 #1
 Join Date
 Apr 2007
 Location
 Lancashire, United Kingdom
 Posts
 264
 Thanks
 32
 Thanked 5 Times in 5 Posts
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

20140402, 06:57 #2
 Join Date
 Apr 2007
 Location
 Lancashire, United Kingdom
 Posts
 264
 Thanks
 32
 Thanked 5 Times in 5 Posts
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

20140402, 10:04 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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 oneanswer 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

20140402, 10:11 #4
 Join Date
 Apr 2007
 Location
 Lancashire, United Kingdom
 Posts
 264
 Thanks
 32
 Thanked 5 Times in 5 Posts
Cheers  that's the conclusion I had come to as well.
I'm still playing

20140402, 10:42 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20140402, 10:54 #6
 Join Date
 Apr 2007
 Location
 Lancashire, United Kingdom
 Posts
 264
 Thanks
 32
 Thanked 5 Times in 5 Posts
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.
AlanLast edited by alan sh; 20140402 at 10:56.

20140402, 16:43 #7
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,982
 Thanks
 157
 Thanked 774 Times in 706 Posts
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

20140402, 20:32 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
The values you get (35) and 15 make sense to me. They are based on leastsquares linear regression [minimizing the square of the deviation in the Yvalues [ie picking a line that minimizes the sum of (YcalcYobs)²].
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

20140402, 22:31 #9
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,982
 Thanks
 157
 Thanked 774 Times in 706 Posts
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.pngLast edited by Maudibe; 20140405 at 11:24.

20140403, 13:23 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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...
SteveLast edited by sdckapr; 20140403 at 14:06.

20140403, 14:07 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Here is a workbook that shows how you can get the polynomial values directly from excel using LINEST function.
Steve