NewFormula = Replace(newFormula, Application.DecimalSeparator, '. NewFormula = Replace(newFormula, 'y =', '') 'Strips 'y =' 'Format the new formula to be understanding by Evaluate() function Trendline.DisplayEquation = trendlineWasVisible 'If you have set the precision, you can set it back here 'Put back the trendline equation like it was before 'Add parenthesis if the formula finishes with a superscript char If preChar = 'x' Or preChar = 'e' Or IsNumeric(preChar) Or preChar = ')' Then If preChar = 'x' Or preChar = 'e' Or preChar = ')' Or IsNumeric(preChar) Then 'If we need to add a '*' before the actual char When dealing with non-linear problems, go-to models include polynomial regression (for example, used for trendline fitting in Microsoft. If Not bCharIsPower And bPreCharIsPower Then If bCharIsPower And Not bPreCharIsPower Then 'Look if the char in written in superscriptīCharIsPower = (i).Font.Superscript TrendlineWasVisible = trendline.DisplayEquationįor i = 1 To Ĭhar = Mid(, i, 1) 'get the actual char Note: Excel transforms the power function approximation into the linear model fitting: the power function y a xb is transformed to a. where: a and b are the parameters of the function found by the least squares method (also named function coefficients or constants ). 'Keep track of the style of the trendline. The power trendline is a curved line described by the function: y a xb. If trendline.Type = xlLogarithmic And xValue <= 0 Then 'If equation is logarithmic and x <= 0, return 0 'If the trendline is a moving average, return 0 Public Function calcTrendlineValueForX(trendline As trendline, xValue As Double) As Double Steps: First, go to the Insert tab and click on the Scatter icon and select the first suggested icon.

You can also find the x-intercept by using the Trendline Equation feature of the Excel graph created from the dataset you have. Result = calcTrendlineValueForX((1).Trendlines(1), x) Using the Trendline Equation Feature of Excel Graph. 'I have a sheet with one scatter plot in sheet 'graph' You may want to set the precision of the Datalabel to suit your needs. I found a solution that works for every type of trendlines (exept for moving average of course).

Excel add trendline equation