Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    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

  2. #2
    3 Star Lounger
    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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    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 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. #4
    3 Star Lounger
    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

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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

  6. #6
    3 Star Lounger
    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.



    Alan
    Last edited by alan sh; 2014-04-02 at 11:56.

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 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

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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 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
    Attached Files Attached Files

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 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.png
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Maudibe; 2014-04-05 at 12:24.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    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...

    Steve
    Attached Files Attached Files
    Last edited by sdckapr; 2014-04-03 at 15:06.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    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
    Attached Files Attached Files

Posting Permissions

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