Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
What's equation for R-Squared value given for chart trendlines?
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
What's equation for R-Squared value given for chart trendlines?
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" wrote: 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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
What's equation for R-Squared value given for chart trendlines
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
What's equation for R-Squared value given for chart trendlines
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" wrote: 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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
What's equation for R-Squared value given for chart trendlines
I'll bite, what is Q-squared? I can find no reference to it in either my
library or Current Index to Statistics. Jerry "Bjoern" wrote: ... (As an aside, there is a better measure of the quality of the fit, called Q-squared). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To find equation for two sets of non contiguous data | Charts and Charting in Excel | |||
Equation Editor- problem when editing an equation | Excel Discussion (Misc queries) | |||
Need Help with #N/A in equation | Excel Discussion (Misc queries) | |||
Averaging/Rounding Equation Problem | Excel Worksheet Functions | |||
Logarithmic Trendline Equation | Charts and Charting in Excel |