how to find the equation of a trendline in excel

Reason: we took the log of x B which results in B*ln(X). Recall that our power model is in the form y=ax Bī =. With this form, it is now possible to fit the regression line with linear regression and use the equation for R Squared.įit a linear model to the new dataset. You might think that the ln(x) part would make linear regression inappropriate, however, linear regression means that it is linear in parameters. This model can now be fit with linear regression. The Power regression model is in the form. However, the power equation can be converted to linear. 6782.Īdditionally, Rsquared is only valid for linear regression Models. The correct equation for Rsq is Also, in the most current version of Excel, the Rsquared for your dataset is. SsReg += math.pow(((intercept * math.pow(xValue, slope)) -yMean), 2).toDouble() SumOfYSquare += math.pow(yValue, 2).toInt() SumOfXSquare += math.pow(xValue, 2).toInt() Int sumOfX = 0,sumOfY = 0,sumOfXY = 0, sumOfXSquare = 0, sumOfYSquare = 0 ĭouble slope = 0.5204, intercept = 2.3133 I have implemented the following code snippet logic to achieve the R squared value List xValue = Here ȳ is the mean value of y and ŷ is the regression equation, here ŷ forĮxponential is y = ae^xb and for power is y = ax^b NewFormula = Replace(newFormula, Application.DecimalSeparator, '.Tried to calculate the R-squared value for the exponential and power trendline type using the following equation. 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

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 'Keep track of the style of the trendline. 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 Result = calcTrendlineValueForX((1).Trendlines(1), x) '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).

How to find the equation of a trendline in excel