We've all been there, you've created a graph in excel, needed to smooth or extrapolate the data so have resorted to doing a quick and dirty trendline.
1
2
=trendlineparse("y = 2.6948ln(x) - 0.4039 ",2)
Public Function TrendlineParse(Eqtn As String, Optional xVal As Variant = "eq", Optional xType As Variant = 0) As Variant 'Function Convert trendline equations into formulas ' (c) Martin Childs 2017 Eqtn = Replace(Eqtn, " ", "") 'Return equation If xVal = "eq" Then TrendlineParse = Eqtn Exit Function End If 'Logarithmic If InStr(Eqtn, "ln(") > 0 Then TrendlineParse = Val(Mid(Eqtn, 3, InStr(Eqtn, "l") - 3)) * Log(xVal) + Val(Mid(Eqtn, InStr(Eqtn, ")") + 1, Len(Eqtn) - InStr(Eqtn, ")"))) Exit Function End If 'Exponential If InStr(Eqtn, "e") > 0 Then TrendlineParse = Val(Mid(Eqtn, 3, InStr(Eqtn, "e") - 3)) * Exp(Val(Mid(Eqtn, InStr(Eqtn, "e") + 1, Len(Eqtn) - 1 - InStr(Eqtn, "e")) * xVal)) Exit Function End If 'Get x positions x1 = InStr(Eqtn, "x") x2 = InStr(x1 + 1, Eqtn, "x") x3 = InStr(x2 + 1, Eqtn, "x") x4 = InStr(x3 + 1, Eqtn, "x") x5 = InStr(x4 + 1, Eqtn, "x") x6 = InStr(x5 + 1, Eqtn, "x") 'Power If LCase(xType) = "power" Or LCase(xType) = "pow" Or xType = "p" Then TrendlineParse = Val(Mid(Eqtn, 3, x1 - 3)) * xVal ^ Val(Mid(Eqtn, x1 + 1, Len(Eqtn) - 1 - x1)) Exit Function End If 'Poly1 If Len(Eqtn) - Len(Replace(Eqtn, "x", "")) = 1 Then TrendlineParse = Val(Mid(Eqtn, 3, x1 - 3)) * xVal + _ Val(Mid(Eqtn, x1 + 1, Len(Eqtn) - 1 - x1)) Exit Function End If 'Poly2 If Len(Eqtn) - Len(Replace(Eqtn, "x", "")) = 2 Then TrendlineParse = Val(Mid(Eqtn, 3, x1 - 3)) * xVal ^ Val(Mid(Eqtn, x1 + 1, 1)) + _ Val(Mid(Eqtn, x1 + 2, x2 - x1 - 2)) * xVal + _ Val(Mid(Eqtn, x2 + 1, Len(Eqtn))) Exit Function End If 'Poly3 If Len(Eqtn) - Len(Replace(Eqtn, "x", "")) = 3 Then TrendlineParse = Val(Mid(Eqtn, 3, x1 - 3)) * xVal ^ Val(Mid(Eqtn, x1 + 1, 1)) + _ Val(Mid(Eqtn, x1 + 2, x2 - x1 - 2)) * xVal ^ Val(Mid(Eqtn, x2 + 1, 1)) + _ Val(Mid(Eqtn, x2 + 2, x3 - x2 - 2)) * xVal + _ Val(Mid(Eqtn, x3 + 1, Len(Eqtn))) Exit Function End If 'Poly4 If Len(Eqtn) - Len(Replace(Eqtn, "x", "")) = 4 Then TrendlineParse = Val(Mid(Eqtn, 3, x1 - 3)) * xVal ^ Val(Mid(Eqtn, x1 + 1, 1)) + _ Val(Mid(Eqtn, x1 + 2, x2 - x1 - 2)) * xVal ^ Val(Mid(Eqtn, x2 + 1, 1)) + _ Val(Mid(Eqtn, x2 + 2, x3 - x2 - 2)) * xVal ^ Val(Mid(Eqtn, x3 + 1, 1)) + _ Val(Mid(Eqtn, x3 + 2, x4 - x3 - 2)) * xVal + _ Val(Mid(Eqtn, x4 + 1, Len(Eqtn))) Exit Function End If 'Poly5 If Len(Eqtn) - Len(Replace(Eqtn, "x", "")) = 5 Then TrendlineParse = Val(Mid(Eqtn, 3, x1 - 3)) * xVal ^ Val(Mid(Eqtn, x1 + 1, 1)) + _ Val(Mid(Eqtn, x1 + 2, x2 - x1 - 2)) * xVal ^ Val(Mid(Eqtn, x2 + 1, 1)) + _ Val(Mid(Eqtn, x2 + 2, x3 - x2 - 2)) * xVal ^ Val(Mid(Eqtn, x3 + 1, 1)) + _ Val(Mid(Eqtn, x3 + 2, x4 - x3 - 2)) * xVal ^ Val(Mid(Eqtn, x4 + 1, 1)) + _ Val(Mid(Eqtn, x4 + 2, x5 - x4 - 2)) * xVal + _ Val(Mid(Eqtn, x5 + 1, Len(Eqtn))) Exit Function End If 'Poly6 If Len(Eqtn) - Len(Replace(Eqtn, "x", "")) = 6 Then TrendlineParse = Val(Mid(Eqtn, 3, x1 - 3)) * xVal ^ Val(Mid(Eqtn, x1 + 1, 1)) + _ Val(Mid(Eqtn, x1 + 2, x2 - x1 - 2)) * xVal ^ Val(Mid(Eqtn, x2 + 1, 1)) + _ Val(Mid(Eqtn, x2 + 2, x3 - x2 - 2)) * xVal ^ Val(Mid(Eqtn, x3 + 1, 1)) + _ Val(Mid(Eqtn, x3 + 2, x4 - x3 - 2)) * xVal ^ Val(Mid(Eqtn, x4 + 1, 1)) + _ Val(Mid(Eqtn, x4 + 2, x5 - x4 - 2)) * xVal ^ Val(Mid(Eqtn, x5 + 1, 1)) + _ Val(Mid(Eqtn, x5 + 2, x6 - x5 - 2)) * xVal + _ Val(Mid(Eqtn, x6 + 1, Len(Eqtn))) Exit Function End If End Function
You can download the spreadhseet here