r2 power regression

Question

What mathematical formula does Excel use to calculate the R2 of a power regression in a graph? While I can reproduce the plotted R2 values of linear and linearized functions by CORREL()^2, PEARSON()^2 and the Analysis Toolpack/Regression, I cannot do this for non-linear regressions. The F1 help-function on "Add a trend or moving average line to a chart" provides the general formula R2 = 1-SSE/SST for Excel 2010 but no specification for later versions and/or non-linear functions.

Thank you for your answer.

Answer 1

What mathematical formula does Excel use to calculate the R2 of a power regression in a graph?

As demonstrated below, we can use RSQ to calculate the power trendline R^2.

(Unfortunately, I cannot provide an Excel file that you can download. Too complicated to explain why.)

The formulas are:

C2: =$G$20 * A2^$E$20

C9: =RSQ(C2:C7, B2:B7)

E20:F24: =LINEST(LN(B2:B7), LN(A2:A7), TRUE, TRUE)

G20: =EXP(F20)

LINEST must be array-entered in some versions of Excel.

The key to using LINEST for this purpose is to realize that the coefficients for the power trendline equation y = b * x^a can be derived from the linear equation ln(y) = ln(b) + a*ln(x). Thus, LINEST calculates "a" in E20, but ln(b) in F20. In order to calculate "b", we must use b = exp(ln(b)) in G20.

But the RSQ calculation does not depend on the linear log equation.

Instead, we calculate "est y" in C2:C7 using the power trendline equation.

R^2 in C9 is simply the squared correlation between actual "y" and "est y".

---

Caveat: The power trendline R^2 varies depending on the version of Excel. For example, in Excel 2010, the chart R^2 is actually the squared correlation between actual ln(y) and estimated ln(y). That was corrected at least in Office 365 Excel. I am told it was corrected in Excel 2019, and perhaps in Excel 2016. (I don't know.)

Likewise, LINEST calculates the correlation between actual ln(y) and estimated ln(y) in E22 (0.350828824713976) because that is the only relationship that it is aware of. LINEST cannot know that we intend to derive the equation y = b * x^a from the linear log equation.

FYI, the value that LINEST calculates in E22 is actually the coefficient of determination (SSreg/SStotal = 1 - SSresid/SStotal), which is designated by r^2, in contrast to the squared Pearson correlation coefficient, which is designated by R^2. But the statistics world often uses the designations interchangeably; and for linear equations, they are the same unless the intercept is zero. So, I use the designation CoD instead of r^2 in order to avoid confusion.

Answer 2

Dear Jan,

Good day! Thank you for posting to Microsoft Community. We are happy to help you.

I'm afraid that R2 may not calculate for non-linear regression. R square may capture explanatory power of independent variables only when there is a linear association between the dependent and independent variables.

I kindly suggest you submit your feedback on the Feedback platform to convey your suggestions and ideas to the product developers directly so that they will know your requirements in your environment for product improvements.

Meanwhile, I will keep this thread open and welcome other community members to share their ideas as well.

Sincerely,

Cliff | Microsoft Community Moderator

***Note: In the event that you're unable to reply to this thread, please ensure that your Email address is verified in the Community Website by clicking on Your Account Name > "My Profile" > "Edit Profile" > Add your Email Address > tick "Receive email notifications" checkbox > click on "Save".***