|
Polynomial trendline
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 |
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 "Xtian" wrote in message ... 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 |
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. -- GeorgeF ------------------------------------------------------------------------ GeorgeF's Profile: http://www.excelforum.com/member.php...o&userid=24124 View this thread: http://www.excelforum.com/showthread...hreadid=391307 |
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: 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. -- GeorgeF ------------------------------------------------------------------------ GeorgeF's Profile: http://www.excelforum.com/member.php...o&userid=24124 View this thread: http://www.excelforum.com/showthread...hreadid=391307 |
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: 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 "Xtian" wrote in message ... 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 |
Hi Bernie,
I've made a mistake during testing. Ewerything works fine. Thank you! Kristijan. Korisnik "Xtian" napisao je: 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: 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 "Xtian" wrote in message ... 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 |
Hi George,
I've made a mistake during testing. Ewerything works fine. Thank you! Kristijan. Korisnik "GeorgeF" napisao je: 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. -- GeorgeF ------------------------------------------------------------------------ GeorgeF's Profile: http://www.excelforum.com/member.php...o&userid=24124 View this thread: http://www.excelforum.com/showthread...hreadid=391307 |
| All times are GMT +1. The time now is 05:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com