Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extracting R-squared value from a chart
Hello,
I have a scatter chart with a power trendline. The R-squared value is displayed. I did find formulae that I can use to calculate the c & b value (for the equation y=c*x^b) directly from the data. My question is whether there is a formula that I can use to either: a-extract the R-squared value from the chart, or b-calculate the R-sqared value directly from the data and equation values (c & b). I hope that makes sense. It's been a looooong time since I've used Statistics, so I may have used some terms incorrectly. If so, I apologize. Let me know and I can clarify. Thanks for the help. -- Mike Lee McKinney,TX USA |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extracting R-squared value from a chart
Mike,
=CORREL(Xvalues,YValues)^2 or =RSQ(YValues,XValues) See also help on LINEST - there is more available in that help than in the CORREL help. HTH, Bernie MS Excel MVP "mikelee101" <mikelee101athotmaildotcom wrote in message ... Hello, I have a scatter chart with a power trendline. The R-squared value is displayed. I did find formulae that I can use to calculate the c & b value (for the equation y=c*x^b) directly from the data. My question is whether there is a formula that I can use to either: a-extract the R-squared value from the chart, or b-calculate the R-sqared value directly from the data and equation values (c & b). I hope that makes sense. It's been a looooong time since I've used Statistics, so I may have used some terms incorrectly. If so, I apologize. Let me know and I can clarify. Thanks for the help. -- Mike Lee McKinney,TX USA |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extracting R-squared value from a chart
Hello Bernie,
Thanks. I did look at RSQ (and CORREL after reading your post), and that seems to apply to linear regression (y=mx+b). My line is y=c*x^b. When I tried both of them, they returned .41...., which was different from the displayed value on the chart. I also read the help on LINEST, and tried adding a couple of columns to my data. I calculated sstotal by squaring the known y's and summing that column. Then in another column calculated ssresid by using my c & b values to calculate the expected y for each known x, taking the difference from the actual y and squaring that, then summing that column. ssreg was sstotal-ssresid. Then I took a shot at r-squared by ssreg/sstotal. Unfortunately, that gave me a value of .93.... when the r-squared value on the chart is displayed at .81.... I suspect that the difference may lie in the fact that I'm using a power line as opposed to a linear one. I hope that makes sense. Since Excel can calculate it in the chart, I have to believe there's a way to calculate it in a cell. The math is just a bit beyond me. Any other suggestions would be greatly appreciated. Thanks again. -- Mike Lee McKinney,TX USA "Bernie Deitrick" wrote: Mike, =CORREL(Xvalues,YValues)^2 or =RSQ(YValues,XValues) See also help on LINEST - there is more available in that help than in the CORREL help. HTH, Bernie MS Excel MVP "mikelee101" <mikelee101athotmaildotcom wrote in message ... Hello, I have a scatter chart with a power trendline. The R-squared value is displayed. I did find formulae that I can use to calculate the c & b value (for the equation y=c*x^b) directly from the data. My question is whether there is a formula that I can use to either: a-extract the R-squared value from the chart, or b-calculate the R-sqared value directly from the data and equation values (c & b). I hope that makes sense. It's been a looooong time since I've used Statistics, so I may have used some terms incorrectly. If so, I apologize. Let me know and I can clarify. Thanks for the help. -- Mike Lee McKinney,TX USA . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extracting R-squared value from a chart
On Fri, 22 Jan 2010 09:18:01 -0800, mikelee101 <mikelee101athotmaildotcom
wrote: Hello, I have a scatter chart with a power trendline. The R-squared value is displayed. I did find formulae that I can use to calculate the c & b value (for the equation y=c*x^b) directly from the data. My question is whether there is a formula that I can use to either: a-extract the R-squared value from the chart, or b-calculate the R-sqared value directly from the data and equation values (c & b). I hope that makes sense. It's been a looooong time since I've used Statistics, so I may have used some terms incorrectly. If so, I apologize. Let me know and I can clarify. Thanks for the help. Did you try using the formula he http://office.microsoft.com/en-us/he...078071033.aspx --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extracting R-squared value from a chart
Try it taking the log of each side:
y = c*x^b log y = log c + b * log x HTH, Bernie MS Excel MVP "mikelee101" <mikelee101athotmaildotcom wrote in message ... Hello Bernie, Thanks. I did look at RSQ (and CORREL after reading your post), and that seems to apply to linear regression (y=mx+b). My line is y=c*x^b. When I tried both of them, they returned .41...., which was different from the displayed value on the chart. I also read the help on LINEST, and tried adding a couple of columns to my data. I calculated sstotal by squaring the known y's and summing that column. Then in another column calculated ssresid by using my c & b values to calculate the expected y for each known x, taking the difference from the actual y and squaring that, then summing that column. ssreg was sstotal-ssresid. Then I took a shot at r-squared by ssreg/sstotal. Unfortunately, that gave me a value of .93.... when the r-squared value on the chart is displayed at .81.... I suspect that the difference may lie in the fact that I'm using a power line as opposed to a linear one. I hope that makes sense. Since Excel can calculate it in the chart, I have to believe there's a way to calculate it in a cell. The math is just a bit beyond me. Any other suggestions would be greatly appreciated. Thanks again. -- Mike Lee McKinney,TX USA "Bernie Deitrick" wrote: Mike, =CORREL(Xvalues,YValues)^2 or =RSQ(YValues,XValues) See also help on LINEST - there is more available in that help than in the CORREL help. HTH, Bernie MS Excel MVP "mikelee101" <mikelee101athotmaildotcom wrote in message ... Hello, I have a scatter chart with a power trendline. The R-squared value is displayed. I did find formulae that I can use to calculate the c & b value (for the equation y=c*x^b) directly from the data. My question is whether there is a formula that I can use to either: a-extract the R-squared value from the chart, or b-calculate the R-sqared value directly from the data and equation values (c & b). I hope that makes sense. It's been a looooong time since I've used Statistics, so I may have used some terms incorrectly. If so, I apologize. Let me know and I can clarify. Thanks for the help. -- Mike Lee McKinney,TX USA . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What's equation for R-Squared value given for chart trendlines? | Charts and Charting in Excel | |||
R squared | Excel Worksheet Functions | |||
how can i make a squared chart area | Charts and Charting in Excel | |||
extracting comments in a cell and making these part of the Chart . | Charts and Charting in Excel | |||
extracting data from seating chart | Excel Worksheet Functions |