Solving a cubic formula or a 3rd degree polynomial equation Does Excel have a fu

Question

Solving a cubic formula or a 3rd degree polynomial equation

Does Excel have a function for solving a cubic formula, or a 3rd order polynomial? I can get a nice, 3rd order polynomial trendline for a regression, but I can't seem to be able to solve for X, based on a known Y. The equation is: y = ax^3 + bx^2 + cx +d. From what I've been able to find, the equation for solving a 3rd degree polynomial is quite complicated. I saw onee suggestion using Excel's goal seek but, since I need to analyze a lot of numbers, this approach isn't practicaal. I hope there might be a built in function for solving a 3rd order polynomial, similar to GROWTH, FORECAST OR LOGEST, but I can't seem to find i!t

Thank-you for your tim

Explanation / Answer

The cubic spline code is:

Function CubicSpline(Xknown As Variant, Yknown As Variant, x As Double)
'****** CubicSpline by SRS1 Software ****** Version 1.01   http://www.srs1software.com
' Revised by Brad Yundt 1/14/10 to handle variant inputs, use proper variable declarations

'Purpose:   Given a set of known x and y values, this function will smoothly interpolate a y value for a specified x

'Note: The known x values must be in ascending order. If they aren't, then errors in the function could occur.

Dim n As Long, nn As Long 'n=input_count
Dim i As Long 'loop counting integers
Dim p As Double, qn As Double, sig As Double, un As Double
Dim h As Double, b As Double, a As Double
Dim xin() As Double, yin() As Double, u() As Double, yt() As Double
Dim v As Variant

Select Case TypeName(Xknown)
Case "Integer()", "Double()", "Variant()"
    n = UBound(Xknown) - LBound(Xknown) + 1
Case "Range"
    n = Xknown.Cells.Count
End Select
Select Case TypeName(Yknown)
Case "Integer()", "Double()", "Variant()"
    nn = UBound(Yknown) - LBound(Yknown) + 1
Case "Range"
    nn = Yknown.Cells.Count
End Select

' Next check to be sure that "input" # points = "output" # points
If n <> nn Then
    CubicSpline = "Number of known x and y values don't match!"
    Exit Function
End If

ReDim xin(1 To n) As Double
ReDim yin(1 To n) As Double
ReDim u(1 To n - 1) As Double
ReDim yt(1 To n) As Double 'these are the 2nd deriv values

i = 0
For Each v In Xknown
    i = i + 1
    xin(i) = v
Next
i = 0
For Each v In Yknown
    i = i + 1
    yin(i) = v
Next

'''''''''''''''''''''''''''''''''''''''
' values are populated
'''''''''''''''''''''''''''''''''''''''
yt(1) = 0
u(1) = 0
qn = 0
un = 0

For i = 2 To n - 1
    sig = (xin(i) - xin(i - 1)) / (xin(i + 1) - xin(i - 1))
    p = sig * yt(i - 1) + 2
    yt(i) = (sig - 1) / p
    u(i) = (yin(i + 1) - yin(i)) / (xin(i + 1) - xin(i)) - (yin(i) - yin(i - 1)) / (xin(i) - xin(i - 1))
    u(i) = (6 * u(i) / (xin(i + 1) - xin(i - 1)) - sig * u(i - 1)) / p
Next i
yt(n) = (un - qn * u(n - 1)) / (qn * yt(n - 1) + 1)

For i = n - 1 To 1 Step -1
    yt(i) = yt(i) * yt(i + 1) + u(i)
Next

''''''''''''''''''''
'now evaluate spline at desired value of x
'''''''''''''''''''''
i = IIf(x >= xin(n), n - 1, Application.Match(x, xin, 1))     'Index of closest point at or below x
h = xin(i + 1) - xin(i)         'x distance between closest points
a = (xin(i + 1) - x) / h        'fraction distance to go to higher point
b = (x - xin(i)) / h            'fraction travelled from lower point

CubicSpline = a * yin(i) + b * yin(i + 1) + ((a ^ 3 - a) * yt(i) + (b ^ 3 - b) * yt(i + 1)) * (h ^ 2) / 6
End Function

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.