Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Excel error in R-squared computation in exponential regression

Excel computes the R-squared of an exponential regression wrongly: instead of
thecorrect result it reports the R-squared of the underlying linera
regression of the logarithm of the data. The two numbers are close if the
correlation is good, put far apart if it isn't. This Excel grossly
exaggerates the R-squared.

I am trying to bring this to the attention of Microsoft. It is of some
importance to me as I will be teaching this topic to my Math class in two
weeks.

So far I have not been able to wade through the layers of bureaucracy.
--
Bjoern Schellenberg

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,393
Default Excel error in R-squared computation in exponential regression

Could you supply more details?
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bjoern" wrote in message
...
Excel computes the R-squared of an exponential regression wrongly: instead
of
thecorrect result it reports the R-squared of the underlying linera
regression of the logarithm of the data. The two numbers are close if the
correlation is good, put far apart if it isn't. This Excel grossly
exaggerates the R-squared.

I am trying to bring this to the attention of Microsoft. It is of some
importance to me as I will be teaching this topic to my Math class in two
weeks.

So far I have not been able to wade through the layers of bureaucracy.
--
Bjoern Schellenberg

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Excel error in R-squared computation in exponential regression

Yes I have an illustratioin that shows it all clearly. Where can I send the
file?
--
Bjoern Schellenberg


"Bernard Liengme" wrote:

Could you supply more details?
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bjoern" wrote in message
...
Excel computes the R-squared of an exponential regression wrongly: instead
of
thecorrect result it reports the R-squared of the underlying linera
regression of the logarithm of the data. The two numbers are close if the
correlation is good, put far apart if it isn't. This Excel grossly
exaggerates the R-squared.

I am trying to bring this to the attention of Microsoft. It is of some
importance to me as I will be teaching this topic to my Math class in two
weeks.

So far I have not been able to wade through the layers of bureaucracy.
--
Bjoern Schellenberg

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Excel error in R-squared computation in exponential regression

I'd be happy to. Where can I send a file that explains it all?
--
Bjoern Schellenberg


"Bernard Liengme" wrote:

Could you supply more details?
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bjoern" wrote in message
...
Excel computes the R-squared of an exponential regression wrongly: instead
of
thecorrect result it reports the R-squared of the underlying linera
regression of the logarithm of the data. The two numbers are close if the
correlation is good, put far apart if it isn't. This Excel grossly
exaggerates the R-squared.

I am trying to bring this to the attention of Microsoft. It is of some
importance to me as I will be teaching this topic to my Math class in two
weeks.

So far I have not been able to wade through the layers of bureaucracy.
--
Bjoern Schellenberg

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Excel error in R-squared computation in exponential regression

I'd be happy to. Where can I send the details?
--
Bjoern Schellenberg


"Bernard Liengme" wrote:

Could you supply more details?
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Bjoern" wrote in message
...
Excel computes the R-squared of an exponential regression wrongly: instead
of
thecorrect result it reports the R-squared of the underlying linera
regression of the logarithm of the data. The two numbers are close if the
correlation is good, put far apart if it isn't. This Excel grossly
exaggerates the R-squared.

I am trying to bring this to the attention of Microsoft. It is of some
importance to me as I will be teaching this topic to my Math class in two
weeks.

So far I have not been able to wade through the layers of bureaucracy.
--
Bjoern Schellenberg

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc






  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,290
Default Excel error in R-squared computation in exponential regression


There has been much discussion on the validity of MS Excel statistical functions.
MS has been beat up pretty badly over it.
MS even admits their R2 chart calculations are incorrect ...
http://support.microsoft.com/kb/829249
"You will receive an incorrect R-squared value in the chart tool in Excel 2003"

Here are a couple of papers that discuss some Excel statistical issues...
http://www.daheiser.info/excel/frontpage.html
http://en.wikibooks.org/wiki/Statist...erics_in_Excel
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Bjoern"
wrote in message
Excel computes the R-squared of an exponential regression wrongly: instead of
thecorrect result it reports the R-squared of the underlying linera
regression of the logarithm of the data. The two numbers are close if the
correlation is good, put far apart if it isn't. This Excel grossly
exaggerates the R-squared.

I am trying to bring this to the attention of Microsoft. It is of some
importance to me as I will be teaching this topic to my Math class in two
weeks.

So far I have not been able to wade through the layers of bureaucracy.
--
Bjoern Schellenberg

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 762
Default Excel error in R-squared computation in exponential regression

Bjoern -

Excel computes the R-squared of an exponential regression wrongly ... <


Yes, but this isn't new news, and in my opinion it's unlikely that Microsoft
will ever fix it. And it does give you an important discussion topic for
your math class.

For the exponential fit, the equation is y = c*exp(b*x). Excel takes the
natural log of both sides to get ln(y) = ln(c)+b*x and does regression with
ln(y) as the dependent variable and x as the explanatory variable, i.e., it
finds ln(c) and b that minimizes the sum of squared deviations between
actual ln(y) and predicted ln(y). So the reported R-squared is the
proportion of variation in ln(y) that can be explained by x, instead of the
proportion of variation in y that is explained by x.

Similarly, for the power fit, the equation is y=c*x^b. Excel takes the
natural log to get ln(y) = ln(c)+b*ln(x) and does regression with ln(y) as
the dependent variable and ln(x) as the explanatory variable. So the
reported R-squared is the proportion of variation in ln(y) that can be
explained by ln(x).

For an extension of your discussions in the math class, you could use Solver
to search for c and b that minimizes the sum of squared deviations between
actual y and predicted y. And so on.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel


"Bjoern" wrote in message
...
Excel computes the R-squared of an exponential regression wrongly: instead
of
thecorrect result it reports the R-squared of the underlying linera
regression of the logarithm of the data. The two numbers are close if the
correlation is good, put far apart if it isn't. This Excel grossly
exaggerates the R-squared.

I am trying to bring this to the attention of Microsoft. It is of some
importance to me as I will be teaching this topic to my Math class in two
weeks.

So far I have not been able to wade through the layers of bureaucracy.
--
Bjoern Schellenberg

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc



  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Excel error in R-squared computation in exponential regression

An excellent reply indeed. I had planned on having students calculate th
R-squared the proper way (after obtaining the parameters as Excel does, but
then plugging them back into the exponential equation and calculating
r-squared as 1-SSR/SSY (where SSR is the sum of the square of the residuals
from regressin and SSY is the sum of the squares of the deviation from the
mean).

I stumbled upon this trying to use exponential regression of world
population as a function of time - clearly not a good model. Graphically it
is obvious that the model is bad, but the R-squared reported was 85%, so I
knew something was wrong. I then found exactly what you stated: Excel reports
the R-squared of the linear regression of the logs and does not bother to
"plug back in and recalculate".

What I fiind surprising is that I seem unable to get the attention of anyone
at Mircosoft to this rather serious flaw.
--
Bjoern Schellenberg


"Mike Middleton" wrote:

Bjoern -

Excel computes the R-squared of an exponential regression wrongly ... <


Yes, but this isn't new news, and in my opinion it's unlikely that Microsoft
will ever fix it. And it does give you an important discussion topic for
your math class.

For the exponential fit, the equation is y = c*exp(b*x). Excel takes the
natural log of both sides to get ln(y) = ln(c)+b*x and does regression with
ln(y) as the dependent variable and x as the explanatory variable, i.e., it
finds ln(c) and b that minimizes the sum of squared deviations between
actual ln(y) and predicted ln(y). So the reported R-squared is the
proportion of variation in ln(y) that can be explained by x, instead of the
proportion of variation in y that is explained by x.

Similarly, for the power fit, the equation is y=c*x^b. Excel takes the
natural log to get ln(y) = ln(c)+b*ln(x) and does regression with ln(y) as
the dependent variable and ln(x) as the explanatory variable. So the
reported R-squared is the proportion of variation in ln(y) that can be
explained by ln(x).

For an extension of your discussions in the math class, you could use Solver
to search for c and b that minimizes the sum of squared deviations between
actual y and predicted y. And so on.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel


"Bjoern" wrote in message
...
Excel computes the R-squared of an exponential regression wrongly: instead
of
thecorrect result it reports the R-squared of the underlying linera
regression of the logarithm of the data. The two numbers are close if the
correlation is good, put far apart if it isn't. This Excel grossly
exaggerates the R-squared.

I am trying to bring this to the attention of Microsoft. It is of some
importance to me as I will be teaching this topic to my Math class in two
weeks.

So far I have not been able to wade through the layers of bureaucracy.
--
Bjoern Schellenberg

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use Excel to do likelihood ratio chi-squared test? Michael Excel Worksheet Functions 2 April 23rd 23 03:43 AM
computation error Rajneesh Arora Excel Discussion (Misc queries) 4 August 22nd 07 08:17 PM
Regression - Standard Error X Variable 1 Claude S Excel Worksheet Functions 2 November 19th 05 10:08 AM
MOD function computation error Marian Megami V Excel Worksheet Functions 7 August 24th 05 09:48 PM


All times are GMT +1. The time now is 04:55 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"