CODEWITHSUNDEEP
excelexcel-formulapolynomials
BasvdBeuken
BasvdBeuken

Reputation: 19

How to use the linest function to calculate a polynomial trendline

So I did some research online to get a Excel formula to calculate a polynomial trendline. And I found the following formula's:

y = (a1 * x^2) + (a2 * x) + b

a1: =INDEX(LINEST(y;x^{1,2});1)
a2: =INDEX(LINEST(y;x^{1,2});1;2)
 b: =INDEX(LINEST(y;x^{1,2});1;3)

But when I try to use these in my excel sheet, I got other values for a1 and a2 than excel gives me when I create a graph with a trendline and display the equation on the chart. and for "b" I got a #REF error. (see the picture below)

example of the situation

Can anyone help me to get this problem solved?

Upvotes: 1

Views: 10913

Answers (2)

Arend
Arend

Reputation: 19

Language tip: if your language is set to european and "." doesnt work, try "" instead of ,. so

    a1: =INDEX(LINEST(y;x^{1\2});1)
    a2: =INDEX(LINEST(y;x^{1\2});1;2)
    b:  =INDEX(LINEST(y;x^{1\2});1;3)

Also just using LINEST is also fine, the results will just spill in multiple boxes.

    LINEST(y;x^{1\2})

I found this tip a few commments deep in another post. Quadratic and cubic regression in Excel

Upvotes: 0

Charles Samson
Charles Samson

Reputation: 1

Due to your language options, it seems the comma is used for decimal numbers. So x^{1,2} is interpreted as a 1xN matrix of x to the power of 1.2. Try using the period instead.

a1: =INDEX(LINEST(y;x^{1.2});1)
a2: =INDEX(LINEST(y;x^{1.2});1;2)
b:  =INDEX(LINEST(y;x^{1.2});1;3)

Screenshot

Upvotes: 0

Related Questions