promotion image of download ymail app
Anonymous asked in Computers & InternetSoftware · 1 decade ago

Excel polynomial trendlines?

On Microsoft Excel, I need to find the unknown data points on a column chart with a polynomial trendline. The data I have is the following: 2005 = 133, 2015 = 73, 2030 = 42. And I need to find the numbers for the years 2006, 2007 etc. Can anyone help?

2 Answers

  • 1 decade ago
    Favorite Answer

    I know this way:

    Prepare a worksheet as below:

    A B C

    1 2005 133

    2 2015 73

    3 2030 42

    4 2007

    5 2006

    Using “Linest” and “Index” function you can find a polynomial equation:

    y = ax^3 + bx^2 + cx + d

    In cell E1 type this formula = Index ( Linest (B1:B3,A1:A3^{1,2,3}),1,1) and press ctrl+shift+enter (this is an array formula)

    Same as above in F1 cell type = Index ( Linest (B1:B3,A1:A3^{1,2,3}),1,2)

    Same as above in G1 cell type = Index ( Linest (B1:B3,A1:A3^{1,2,3}),1,3)

    Same as above in H1 cell type = Index ( Linest (B1:B3,A1:A3^{1,2,3}),1,4)

    As you can see the coefficients of polynomial equations are:

    a : Cell E1

    b : Cell E1

    c : Cell E1

    d : Cell E1

    and your answer is :

    a= 0.0000523315308374324

    b= -0.159272428233135


    d= 218612.148487136

    Your equation is ready. So with a simple formula on column “C” you can evaluate the number for each year as below:

    Cell c1: =($E$1*A1^3)+($F$1*A1^2)+($G$1*A1)+$H$1

    Cell c2: =($E$1*A2^3)+($F$1*A2^2)+($G$1*A2)+$H$1

    Cell c3: =($E$1*A3^3)+($F$1*A3^2)+($G$1*A3)+$H$1

    Cell c4: =($E$1*A4^3)+($F$1*A4^2)+($G$1*A4)+$H$1

    Cell c5: =($E$1*A5^3)+($F$1*A5^2)+($G$1*A5)+$H$1

    Finally as you see the answer for year 2007 is 118.501924669952 and for year 2006 is 125.595303610549.

    Good Luck!

    Source(s): I am an excel adict.
    • Commenter avatarLogin to reply the answers
  • 3 years ago

    Polynomial Trendline Excel

    • Commenter avatarLogin to reply the answers
Still have questions? Get your answers by asking now.