Polynomial trendline

[Guest]

y=ax^2+bx+c

I know the constants a,b, and c.
I also know the value of the function y.

How can I calculate the x value with the help of Excel functions?

Thank you!

Kristijan

 

[Bernie Deitrick]

Kristijan,

From basic algebra, the two solutions for a quadratic equation of the form 0 = ax^2+bx+c are

x = (-b + sqrt(b^2-4ac))/2a and
x = (-b - sqrt(b^2-4ac))/2a

Since you have y=ax^2+bx+c, you simply need to get it into standard form, by subtracting y from each
side, so your two solutions are

x = (-b + sqrt(b^2-4a(c-y)))/2a and
x = (-b - sqrt(b^2-4a(c-y)))/2a

You can change a, b, c, and y to cell references, and use Excel's SQRT function to solve it.

HTH,
Bernie
MS Excel MVP

 

[GeorgeF]

Hi Kristijan,
In your expression of the 2-power equation, you state that you know, a,
b, c, and Y. Reexpess your equation as a typical quadratic as:

aX^2+bX+C, where C = c-Y

The only unknown in the above is X. Place the values for a, b, and the
new C in three cells A1, B1, and C1.

In D1 write: =(-B1+(B1^2-4*A1*C1)^0.5)/(2*A1)

In E1 write: =(-B1-(B1^2-4*A1*C1)^0.5)/(2*A1)

D1 and E1 are the two numerical solutions of X and your judgement will
tell you of their reality
I hope this helps. GeorgeF.

 

[Guest]

Thank you George,
I have calculated the x value by the equations, but Excel doesn't give the
right answers. Also the a,b, and c constants differ when I calculate them
with =LINEST(y,x^{1,2}) function and compare them with the equation displayed
on the chart. The difference is not big (10^ -13) but never the less.
Here are my test data:
0 104.24
4 103.88
7 103.57
11 103.23
14 103.09
18 103.04
21 102.97
25 102.81
28 102.80

First I have calculated the constants with the LINEST function on above set
of data, and then I have tried to calculated the x values from calculated
constants and the y value. The errors are quite big for the first data pair,
and they are getting bigger for other data pairs. For the last two data pairs
Excel gives the #DIV! error, so something is deffinitely wrong somewhere. If
you know how to fix that I would be wery gratefful. Thank you.
Kristijan.


Korisnik "GeorgeF" napisao je:

 

[Guest]

Thank you Bernie,
I have calculated the x value by the equations, but Excel doesn't give the
right answers. Also the a,b, and c constants differ when I calculate them
with =LINEST(y,x^{1,2}) function and compare them with the equation displayed
on the chart. The difference is not big (10^ -13) but never the less.
Here are my test data:
0 104.24
4 103.88
7 103.57
11 103.23
14 103.09
18 103.04
21 102.97
25 102.81
28 102.80

First I have calculated the constants with the LINEST function on above set
of data, and then I have tried to calculated the x values from calculated
constants and the y value. The errors are quite big for the first data pair,
and they are getting bigger for other data pairs. For the last two data pairs
Excel gives the #DIV! error, so something is deffinitely wrong somewhere. If
you know how to fix that I would be wery gratefful. Thank you.
Kristijan.


Korisnik "Bernie Deitrick" napisao je:

 

[Guest]

Hi Bernie,
I've made a mistake during testing. Ewerything works fine. Thank you!
Kristijan.

Korisnik "Xtian" napisao je:

 

[Guest]

Hi George,
I've made a mistake during testing. Ewerything works fine. Thank you!
Kristijan.

Korisnik "GeorgeF" napisao je: