Excel - Looking to reverse a quadratic formula, Sqrt and all??

Asked By DataScheme on 14-Aug-09 08:34 AM
I know there is a trend line avail. with poly fit and you can change to
numbers.
However when reversing this formula to solve for X there is no help in
excel.  Is there an add in or someone whith a spreadsheet that can help?

Simple Quadratic help would do?




GarysStuden replied on 14-Aug-09 09:17 AM
with the usual coefficients:
a in A1
b in B1
c in C1

use:

=(-B1+(B1*B1-4*A1*C1)^0.5)/(2*A1)
=(-B1-(B1*B1-4*A1*C1)^0.5)/(2*A1)


For for A1 thru C1:

10	60	30

the formulas return the Real roots:

-0.550510257
-5.449489743

For non-Real roots you must use more "imaginative" methods.
--
Gary''s Student - gsnu200860
Joe replied on 14-Aug-09 09:27 AM
Use the quadratic formula

with A in A1, b in B1, c in C1

=-b +/- sqrt(b**2-4ab)
__________________
2a


=IF(B1^2-(4*A1*B1)>=0,(-1*B1)+SQRT(B1^2-(4*A1*B1))/(2*A1),(-1*B1/2*A1)&"+"&SQRT((4*A1*B1)-B1^2)/(2*A1)&"i")

and

=IF(B1^2-(4*A1*B1)>=0,(-1*B1)-SQRT(B1^2-(4*A1*B1))/(2*A1),(-1*B1/2*A1)&"-"&SQRT((4*A1*B1)-B1^2)/(2*A1)&"i")
Luke replied on 14-Aug-09 09:36 AM
To calculate non real roots:

Real1:
=(-B1+IF(B1^2-4*A1*C1<0,0,B1^2-4*A1*C1)^0.5)/(2*A1)
Imaginary1 (if needed):
=IF(B1^2-4*A1*C1<0,(-1*(B1^2-4*A1*C1))^0.5/(2*A1),"")
Real2:
=(-B1-IF(B1^2-4*A1*C1<0,0,B1^2-4*A1*C1)^0.5)/(2*A1)
Imaginary2 (if needed):
=IF(B1^2-4*A1*C1<0,(-1*(B1^2-4*A1*C1))^0.5/-(2*A1),"")


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*