Comments on: Excel trendline types, equations and formulas

It is very easy to add a trendline in Excel. The only real challenge is to choose the trendline type that best corresponds to the type of data you are analyzing. In this tutorial, you will find the detailed description of all the trendline options available in Excel and when to use them. Continue reading →

Comments page 2. Total comments: 75

  1. Thank you! This was a big help

  2. Hi Svetlana,
    May I ask what does ^{1,2} and ^{1,2,3} stands for in Polynomial trendline formulas b2: =INDEX(LINEST(y, x^{1,2}), 1) and b3: =INDEX(LINEST(y, x^{1,2,3}), 1) ?

    1. Hi Mijo,

      In Excel, the caret (^) is used for exponentiation, i.e. to raise a number to power. In our case, x^{1,2} raises each x value to the power of 1 and to the power of 2.

  3. Hello

    I am using the power trend line, and the equation is like this

    y=a x^b ,

    i want to fix b to have a value of 0.5
    and the only changing is a.

    any way how to do this.

    thanks in advance

  4. How to get equation for trigonometric (sinousoidal) graph in excel or Origin

  5. Fantastic advice all round with this article-many thanks.

    About exponential trendline it appears =RSQ(Ln(Known_y's),(Known_x's)) to calculate R^2

  6. Very helpful! thank you

  7. Fantastic help - Thank you

  8. Hi,

    I have an equation must be solved by excel but im not aure what function to use. The question give me the following 4+y/(2x+y)^2+12. The given X=4 and y=6. And they said the X value and Y value cells i have to rename to be cell of 4 name box (x) and 6 name box (y) . What function i have to use to enter to be solved. Hi t the basic calculation method not accepted as manual. I tried normative and others but i cant reach a solution.

    Please advice.

  9. super helpful!
    spasibo!

  10. Hi Svetlana,
    Thank you for your sharing. I have applied the formula to find the coefficients of a polynomial trendline with degree 6 but the answer turns out to be wrong. Can you help me to solve this?

  11. Hi Svetlana,
    I would like to ask if I want to find the coefficients of a polynomial trendline with degree 6, how should I do that? I applied the formula above and the answer was worry. Can u help me?

  12. No matter how many times you fail, you must face life and be full of hope

  13. This is very useful, thank you!
    I did get the formulas to work; however, I have a challenge with empty cells. I'm pulling data from a pivot table for a number interconnected y-values (ie. the sum of the y's should sum to a total) but there are voids throughout. If the array contains a void, the formula doesn't work.
    Is there an easy way around this?

  14. Hi,

    How can I make x=y in scatter plots in excel? I have two data series.

  15. Thank you , this is clear, please share with us how to get the second part.

  16. Thank you , this is clear, where is the second part.

  17. what can i use if my data string includes values of 0?

  18. I have problem with trendline equation. The X value in the equation is not the same scale as the input values. My input X values are 2000, 3000, 4000, ..., but Excel use number 1,2,3, ... as the input value. How to correct the problem.

  19. How do I retrieve the real values of a Trendline in the easiest way for example peak values etc?

    Thanks in advance
    Bo

  20. This was a good explanation, but what I find impossible to find is how to calculate R2 for some of these curves (like logarithmic and power)

  21. Hi Svetlana,
    I tried to lug the equation back to re-test the equation, but the result isn't the same. Can you help to explain why and how to fix it? Thanks.

    1. Hi Henry,

      It's difficult to make any assumptions without seeing your data. The only thing that comes to mind is that Excel displays only a few significant digits in a trendline equation for the sake of space. You can try to display more digits as explained in How to show more decimal places in a trendline equation.

      Also, please keep in mind that an trendline equation is correct only in scatter charts because only this chart type plots both the y-axis and x-axis as numeric values.

      1. I tested all the formulas and compared them against the graph results and it's all working perfect. There are some variances as the graph formulas are rounded up which means those formulas are more accurate than the graph results.

        Svetlana,
        Thanks a lot for those amazing tips. It saved me a lot of time building charts.

  22. Is it possible to use an IF condition to control the display of a trendline on a scatterplot?, for example something along the lines of:
    " =IF(A1==1,Show exponential trendline, IF(A1==2,show logarithmic trenline, show linear trendline)) "

  23. Works great, Thanks Светлана!
    Is there any way to get R2 in a sheet cells, without building a chart and trendlines? E.g. you are saying polinominal 2 is more accurate than polinominal 3, but without comparing R2 you can not tell this. I need to show my boss that we have compared all different trendlines for this particular data and the range of R2 is as follow.. It is ok also if the answer will be no it can not be done...
    This is very helpful website - thanks a lot for all you are doing.
    Sergey.

    1. Hi Sergey,

      The good news is that Excel has a built-in function to find R-Squared:
      RSQ(known_y's,known_x's)

      And thank you for your kind words!

      1. Svetlana,
        Thanks for your quick reply, the RSQ formula returns the square of the Pearson product moment correlation, but Pearson equation is only for linear correlations. SO I will not be able to use this equation to get R2 for the polinominal or any other trendlines except of the linear and compare the R2 for different trendlines.

        Thanks,

          1. Thanks a lot Svetlana, Spencer & Kinia.
            The R2 formulas are working like a charm for linear, ln, & polynomial trends.
            Do you know what's the R2 formula for Exponential trend as well?

            1. First off, this whole post was extremely useful so thank you!
              Not sure about exponential, but for the power trendline you can use =RSQ(LOG10(Known_y's),LOG10(Known_x's)) to calculate R^2

        2. Sergey,
          You are absolutely right. Sorry for my hasty response, I overlooked that you were looking to compare R2 of all different trendlines. In this case, I do not know a way to get R2 other than by building a chart.

          1. For logarithmic equations use
            =RSQ(knownY's,LN(knownX's))
            However, I am not sure on polynomials.

  24. Hi, thank you for sharing this. It is so helpful!

  25. A clear and very useful explanation. Thank you Svetlana.

    Is there a way to get Excel to output the values of the trendline parameters (a and b) to cells in the workbook?

    1. Hi Jeremy,
      This can only be done with Excel functions: SLOPE and INTERCEPT for a linear trendline and more complex formulas for other trend line types. You can find all the formulas in the second part of this tutorial (Excel trendline equations and formulas).

  26. Very useful. Thank you Svetlana:)

  27. This is very useful information for even experts on Excel.
    Am impressed with the write up.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)