vbNewbie2009
asked on
Coefficients 2nd Order Polynomial Curve
I have a recordset of x an y values that I want to use to return the coefficients that would be used to equate 'y' for a 2nd order polynomial curve. Is there a SQL function that will return those?
ozo
No, you would have to define your own function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
vbNewbie2009,
I want make sure you can see how easy this solution is to apply. Here is vba version of it if you are not comfortable with TSQL
Test: we have three points of polynomial
X^2 – 2x + 2 = y
P1(1, 1), P2(0,2), P3(2, 2)
Now, plugging these six values into the solution:
You get a: 1, b: -2, and c: 2
Also, depending how it is going to be used, you can put the tsql from my first post in a stored procedure.
Mike
I want make sure you can see how easy this solution is to apply. Here is vba version of it if you are not comfortable with TSQL
Test: we have three points of polynomial
X^2 – 2x + 2 = y
P1(1, 1), P2(0,2), P3(2, 2)
Now, plugging these six values into the solution:
Sub PolyTest()
Dim p1x As Single: p1x = 1
Dim p1y As Single: p1y = 1
Dim p2x As Single: p2x = 0
Dim p2y As Single: p2y = 2
Dim p3x As Single: p3x = 2
Dim p3y As Single: p3y = 2
Dim a As Single
Dim b As Single
Dim c As Single
a = (p3y * p2x - p1y * p2x - p3y * p1x + p1y * p1x - p2y * p3x + p1y * p3x + p2y * p1x - p1y * p1x) / (-p2x * p2x * p3x + p1x * p1x * p3x + p2x * p2x * p1x - p1x * p1x * p1x + p3x * p3x * p2x - p1x * p1x * p2x - p3x * p3x * p1x + p1x * p1x * p1x)
b = ((p2y - a * p2x * p2x - p1y + a * p1x * p1x) / (p2x - p1x))
c = p1y - a * p1x * p1x - b * p1x
MsgBox "a: " & a & ", b: " & b & ", c: " & c
End SubYou get a: 1, b: -2, and c: 2
Also, depending how it is going to be used, you can put the tsql from my first post in a stored procedure.
Mike
ASKER
This base formula worked great; thank you!