Be careful when you use Excel for calculating the R-squared in an exponential
model: Excel uses the wrong formula, and the results can be dramaticlly wrong.
Here is the story.
Given amy model equation y=f(x) and real data (xi,yi), RSQ (R-squared) is
defined as 1-SSR/SSY where SSR is the sum of the squares of teh residuals
yi-f(xi) and SSY is the sum of the squares of the differences yi-mean(y).
Thus R-squared is a measure of how much variation is attributable to the
model versus random fluctuations, or in other words how well the model tracks
the data. (As an aside, there is a better measure of the quality of the fit,
called Q-squared).
For a linear model y=mx+b, Excel calculates the R-squared correctly.
For an exponential model y=a*exp(bx), Excel uses the convenience of
determining a and b by linera regression of ln(y)=ln(a)+b*ln(x). While this
shortcut gives values for a and b that are slightly incorrect, the procedure
is definsible because 1) the discrepancies are usually small and 2) linear
regression has the advantage of being done with matric algebra, and always
gives the exact solution, whereas teh "true" ecponential regression (finding
a and b to minimize SSR) must be done with algorithms that do not always
work, that sometimes roll over and die.
The problem is that Excel should then to back to the original model equation
and do the R-squared calculation. For reasons that I cannot fathom, they fail
to do that, and the discrepancies can be huge. I recently did an exponential
regression on some UN data. Excel repoirted an R-sqaured of 85% but the
correct value was near 20%,
--
Bjoern Schellenberg
Sarah said:
that would make sense- 'help' seemed a little illusive on the whole thing,
trying to hide it's own short cut.
Brilliant help, thank you!!
B. R.Ramachandran said:
Hi,
Try this:
=RSQ(ln(y-range),x-range) [e.g., =RSQ(LN(B2:B12),A2:A12), where A2:A12 and
B2:B12 contain the x- and y- data respectively]
It seems to me that, while fitting an exponential trendline, the Chart
Wizard actually computes the natural logarithm of the y-values, and performs
a linear fit on the ln(y) vs x data. So the R-Squared value that the
exponential trendline returns is actually the R-Squared value of the
linear-transform. That's why "Help" probably calls it the "transformed
regression model".
Regards,
B. R. Ramachandran
Sarah said:
I am using an exponential trendline in an Excel Chart and want to replicate
the R-Squared value the chart wizard gives. I know that RSQ would give me
this were I dealing with a linear trend. However, with exponential Microsoft
Help just says a "transformed regression model".
I was hoping someone could give me the equation, or even better offer a
function that calculates it.
Thanks
