2008/11/13

Exponential regression in Excel

As it turned out, Excel calculates the R-squared values of exponential regression based on transformed data. For example, for a exponential curve y=ae^bx, Excel computes R^2 using ln(y) = ln(a) + bx.

I know I shouldn't rely on Excel to do my stats but occasionally I do use it to do some quick analyses before pulling out R. What happened today was I did the regression in Excel first to see the plot and then calculate the P-value in R. Luckily, I caught the discrepancy in the R-squared value and decided to find out what the problem was. It took me quite some time to figure it out, and needless to say, I am not happy about it.

Lack of capability is one thing; Pretending being able to do something and then screw it up is totally another.

[Edit to add on 03/16/2010]
I am a bit surprised to find that this appears to be one of the most read entries of my blog. Lots of people landed here through googling some combination of "excel", "regression", and other words.
Anyway, I have a slightly more detailed explanation of the problem with performing exponential regression in Excel, plus some simple tutorials of doing regression analysis in R on my wiki at:

http://wiki.chkuo.name/

Feel free to take a look there if you are interested, hope this helps.

No comments: