|
R squared
Hi Group,
I am in "Microsoft Excel Data Analysis and Business Modeling." Chapter 42, pg 342-343 and put the R squared value on a chart, which equals .9828. Then i went to the functions and did RSQ, thinking this would give me the same answer, but it did not. Maybe I am using the wrong function, although it indicates that is give the R squared value, but the anser I get is .8386 The data: Year Sales 1 70 2 183 3 340 4 649 5 1243 6 1979 7 4096 8 6440 9 8459 10 12154 The graph gives one answer, (Format options on the trend line, "display R squared value on chart") = .9828, but the RSQ function gives .8386. -- David |
R squared
maybe some engineers in the club can join your query...
looking for the meaning of R square... regards, driller ***** - dive with Jonathan Seagull "David" wrote: Hi Group, I am in "Microsoft Excel Data Analysis and Business Modeling." Chapter 42, pg 342-343 and put the R squared value on a chart, which equals .9828. Then i went to the functions and did RSQ, thinking this would give me the same answer, but it did not. Maybe I am using the wrong function, although it indicates that is give the R squared value, but the anser I get is .8386 The data: Year Sales 1 70 2 183 3 340 4 649 5 1243 6 1979 7 4096 8 6440 9 8459 10 12154 The graph gives one answer, (Format options on the trend line, "display R squared value on chart") = .9828, but the RSQ function gives .8386. -- David |
R squared
David -
The chart uses an exponential function for the fit, and the chart trendline's R^2 value is based on that fit (which involves a log transformation of the Y data). The RSQ worksheet function is equivalent to using a linear function for the fit (involving no transformation). If you want more details about the differences, let me know, and I can send you some excerpts from my book "Data Analysis Using Microsoft Excel: Updated for Office XP." - Mike http://www.MikeMiddleton.com "David" wrote in message ... Hi Group, I am in "Microsoft Excel Data Analysis and Business Modeling." Chapter 42, pg 342-343 and put the R squared value on a chart, which equals .9828. Then i went to the functions and did RSQ, thinking this would give me the same answer, but it did not. Maybe I am using the wrong function, although it indicates that is give the R squared value, but the anser I get is .8386 The data: Year Sales 1 70 2 183 3 340 4 649 5 1243 6 1979 7 4096 8 6440 9 8459 10 12154 The graph gives one answer, (Format options on the trend line, "display R squared value on chart") = .9828, but the RSQ function gives .8386. -- David |
R squared
Hi Mike,
Should I be using a different function than RSQ to come up with the answer? If RSQ is for a linear function, what might I use for an exponential growth? I would be interested in your book, could you provide the ISBN. Thank you for your help. -- David "Mike Middleton" wrote: David - The chart uses an exponential function for the fit, and the chart trendline's R^2 value is based on that fit (which involves a log transformation of the Y data). The RSQ worksheet function is equivalent to using a linear function for the fit (involving no transformation). If you want more details about the differences, let me know, and I can send you some excerpts from my book "Data Analysis Using Microsoft Excel: Updated for Office XP." - Mike http://www.MikeMiddleton.com "David" wrote in message ... Hi Group, I am in "Microsoft Excel Data Analysis and Business Modeling." Chapter 42, pg 342-343 and put the R squared value on a chart, which equals .9828. Then i went to the functions and did RSQ, thinking this would give me the same answer, but it did not. Maybe I am using the wrong function, although it indicates that is give the R squared value, but the anser I get is .8386 The data: Year Sales 1 70 2 183 3 340 4 649 5 1243 6 1979 7 4096 8 6440 9 8459 10 12154 The graph gives one answer, (Format options on the trend line, "display R squared value on chart") = .9828, but the RSQ function gives .8386. -- David |
R squared
LINEST(,,,TRUE) and LOGEST(,,,TRUE) provide the R^2 (3rd row, first column of
output) for the specified model. Jerry "David" wrote: Hi Mike, Should I be using a different function than RSQ to come up with the answer? If RSQ is for a linear function, what might I use for an exponential growth? I would be interested in your book, could you provide the ISBN. Thank you for your help. -- David "Mike Middleton" wrote: David - The chart uses an exponential function for the fit, and the chart trendline's R^2 value is based on that fit (which involves a log transformation of the Y data). The RSQ worksheet function is equivalent to using a linear function for the fit (involving no transformation). If you want more details about the differences, let me know, and I can send you some excerpts from my book "Data Analysis Using Microsoft Excel: Updated for Office XP." - Mike http://www.MikeMiddleton.com "David" wrote in message ... Hi Group, I am in "Microsoft Excel Data Analysis and Business Modeling." Chapter 42, pg 342-343 and put the R squared value on a chart, which equals .9828. Then i went to the functions and did RSQ, thinking this would give me the same answer, but it did not. Maybe I am using the wrong function, although it indicates that is give the R squared value, but the anser I get is .8386 The data: Year Sales 1 70 2 183 3 340 4 649 5 1243 6 1979 7 4096 8 6440 9 8459 10 12154 The graph gives one answer, (Format options on the trend line, "display R squared value on chart") = .9828, but the RSQ function gives .8386. -- David |
R squared
Hi Jerry,
Since it is exponential, I used Longest to get the Ratio, but I am trying to derive the numbers for the equation that is used and R squared. R2 = 0.9828 and the equation is y = 58.553e0.5694x. I do not know if Excel has functions to find R2, the 58.553 and .5694. y=ae^bx is the general equation. It appears that the only way to find these numbers is to find them by using the options for the trend line, but a series of steps must underlie deriving these numbers for them to appear. That is what I am trying to do. Thank you for your times and efforts. -- David "Jerry W. Lewis" wrote: LINEST(,,,TRUE) and LOGEST(,,,TRUE) provide the R^2 (3rd row, first column of output) for the specified model. Jerry "David" wrote: Hi Mike, Should I be using a different function than RSQ to come up with the answer? If RSQ is for a linear function, what might I use for an exponential growth? I would be interested in your book, could you provide the ISBN. Thank you for your help. -- David "Mike Middleton" wrote: David - The chart uses an exponential function for the fit, and the chart trendline's R^2 value is based on that fit (which involves a log transformation of the Y data). The RSQ worksheet function is equivalent to using a linear function for the fit (involving no transformation). If you want more details about the differences, let me know, and I can send you some excerpts from my book "Data Analysis Using Microsoft Excel: Updated for Office XP." - Mike http://www.MikeMiddleton.com "David" wrote in message ... Hi Group, I am in "Microsoft Excel Data Analysis and Business Modeling." Chapter 42, pg 342-343 and put the R squared value on a chart, which equals .9828. Then i went to the functions and did RSQ, thinking this would give me the same answer, but it did not. Maybe I am using the wrong function, although it indicates that is give the R squared value, but the anser I get is .8386 The data: Year Sales 1 70 2 183 3 340 4 649 5 1243 6 1979 7 4096 8 6440 9 8459 10 12154 The graph gives one answer, (Format options on the trend line, "display R squared value on chart") = .9828, but the RSQ function gives .8386. -- David |
R squared
David -
You could use =RSQ(LN(known_y's),known_x's). Or, you could use the array-entered LOGEST function suggested by Jerry W. Lewis. ISBN-10: 0534402933 ISBN-13: 9780534402938 - Mike "David" wrote in message ... Hi Mike, Should I be using a different function than RSQ to come up with the answer? If RSQ is for a linear function, what might I use for an exponential growth? I would be interested in your book, could you provide the ISBN. Thank you for your help. -- David "Mike Middleton" wrote: David - The chart uses an exponential function for the fit, and the chart trendline's R^2 value is based on that fit (which involves a log transformation of the Y data). The RSQ worksheet function is equivalent to using a linear function for the fit (involving no transformation). If you want more details about the differences, let me know, and I can send you some excerpts from my book "Data Analysis Using Microsoft Excel: Updated for Office XP." - Mike http://www.MikeMiddleton.com "David" wrote in message ... Hi Group, I am in "Microsoft Excel Data Analysis and Business Modeling." Chapter 42, pg 342-343 and put the R squared value on a chart, which equals .9828. Then i went to the functions and did RSQ, thinking this would give me the same answer, but it did not. Maybe I am using the wrong function, although it indicates that is give the R squared value, but the anser I get is .8386 The data: Year Sales 1 70 2 183 3 340 4 649 5 1243 6 1979 7 4096 8 6440 9 8459 10 12154 The graph gives one answer, (Format options on the trend line, "display R squared value on chart") = .9828, but the RSQ function gives .8386. -- David |
R squared
Hi Mike,
Thank you. This did find R2. Now I am trying to find the other numbers used in the formula. Thank you for your help. -- David "Mike Middleton" wrote: David - You could use =RSQ(LN(known_y's),known_x's). Or, you could use the array-entered LOGEST function suggested by Jerry W. Lewis. ISBN-10: 0534402933 ISBN-13: 9780534402938 - Mike "David" wrote in message ... Hi Mike, Should I be using a different function than RSQ to come up with the answer? If RSQ is for a linear function, what might I use for an exponential growth? I would be interested in your book, could you provide the ISBN. Thank you for your help. -- David "Mike Middleton" wrote: David - The chart uses an exponential function for the fit, and the chart trendline's R^2 value is based on that fit (which involves a log transformation of the Y data). The RSQ worksheet function is equivalent to using a linear function for the fit (involving no transformation). If you want more details about the differences, let me know, and I can send you some excerpts from my book "Data Analysis Using Microsoft Excel: Updated for Office XP." - Mike http://www.MikeMiddleton.com "David" wrote in message ... Hi Group, I am in "Microsoft Excel Data Analysis and Business Modeling." Chapter 42, pg 342-343 and put the R squared value on a chart, which equals .9828. Then i went to the functions and did RSQ, thinking this would give me the same answer, but it did not. Maybe I am using the wrong function, although it indicates that is give the R squared value, but the anser I get is .8386 The data: Year Sales 1 70 2 183 3 340 4 649 5 1243 6 1979 7 4096 8 6440 9 8459 10 12154 The graph gives one answer, (Format options on the trend line, "display R squared value on chart") = .9828, but the RSQ function gives .8386. -- David |
R squared
David -
For an equation of the form Y = A*EXP(B*X), Excel's exponential trendline can be determined using these worksheet formulas: A =EXP(INTERCEPT(LN(known_y's),known_x's)) B =SLOPE(LN(known_y's),known_x's). - Mike "David" wrote in message ... Hi Mike, Thank you. This did find R2. Now I am trying to find the other numbers used in the formula. Thank you for your help. -- David "Mike Middleton" wrote: David - You could use =RSQ(LN(known_y's),known_x's). Or, you could use the array-entered LOGEST function suggested by Jerry W. Lewis. ISBN-10: 0534402933 ISBN-13: 9780534402938 - Mike "David" wrote in message ... Hi Mike, Should I be using a different function than RSQ to come up with the answer? If RSQ is for a linear function, what might I use for an exponential growth? I would be interested in your book, could you provide the ISBN. Thank you for your help. -- David "Mike Middleton" wrote: David - The chart uses an exponential function for the fit, and the chart trendline's R^2 value is based on that fit (which involves a log transformation of the Y data). The RSQ worksheet function is equivalent to using a linear function for the fit (involving no transformation). If you want more details about the differences, let me know, and I can send you some excerpts from my book "Data Analysis Using Microsoft Excel: Updated for Office XP." - Mike http://www.MikeMiddleton.com "David" wrote in message ... Hi Group, I am in "Microsoft Excel Data Analysis and Business Modeling." Chapter 42, pg 342-343 and put the R squared value on a chart, which equals .9828. Then i went to the functions and did RSQ, thinking this would give me the same answer, but it did not. Maybe I am using the wrong function, although it indicates that is give the R squared value, but the anser I get is .8386 The data: Year Sales 1 70 2 183 3 340 4 649 5 1243 6 1979 7 4096 8 6440 9 8459 10 12154 The graph gives one answer, (Format options on the trend line, "display R squared value on chart") = .9828, but the RSQ function gives .8386. -- David |
R squared
Thank you for your help. Looks like I will not get to try this until tomorrow.
Thanks again, -- David "Mike Middleton" wrote: David - For an equation of the form Y = A*EXP(B*X), Excel's exponential trendline can be determined using these worksheet formulas: A =EXP(INTERCEPT(LN(known_y's),known_x's)) B =SLOPE(LN(known_y's),known_x's). - Mike "David" wrote in message ... Hi Mike, Thank you. This did find R2. Now I am trying to find the other numbers used in the formula. Thank you for your help. -- David "Mike Middleton" wrote: David - You could use =RSQ(LN(known_y's),known_x's). Or, you could use the array-entered LOGEST function suggested by Jerry W. Lewis. ISBN-10: 0534402933 ISBN-13: 9780534402938 - Mike "David" wrote in message ... Hi Mike, Should I be using a different function than RSQ to come up with the answer? If RSQ is for a linear function, what might I use for an exponential growth? I would be interested in your book, could you provide the ISBN. Thank you for your help. -- David "Mike Middleton" wrote: David - The chart uses an exponential function for the fit, and the chart trendline's R^2 value is based on that fit (which involves a log transformation of the Y data). The RSQ worksheet function is equivalent to using a linear function for the fit (involving no transformation). If you want more details about the differences, let me know, and I can send you some excerpts from my book "Data Analysis Using Microsoft Excel: Updated for Office XP." - Mike http://www.MikeMiddleton.com "David" wrote in message ... Hi Group, I am in "Microsoft Excel Data Analysis and Business Modeling." Chapter 42, pg 342-343 and put the R squared value on a chart, which equals .9828. Then i went to the functions and did RSQ, thinking this would give me the same answer, but it did not. Maybe I am using the wrong function, although it indicates that is give the R squared value, but the anser I get is .8386 The data: Year Sales 1 70 2 183 3 340 4 649 5 1243 6 1979 7 4096 8 6440 9 8459 10 12154 The graph gives one answer, (Format options on the trend line, "display R squared value on chart") = .9828, but the RSQ function gives .8386. -- David |
R squared
Hi David,
I think you will find this link very handy. I know I did (do!) http://j-walk.com/ss///excel/tips/tip101.htm HTH Martin "David" wrote in message ... Thank you for your help. Looks like I will not get to try this until tomorrow. Thanks again, -- David "Mike Middleton" wrote: David - For an equation of the form Y = A*EXP(B*X), Excel's exponential trendline can be determined using these worksheet formulas: A =EXP(INTERCEPT(LN(known_y's),known_x's)) B =SLOPE(LN(known_y's),known_x's). - Mike "David" wrote in message ... Hi Mike, Thank you. This did find R2. Now I am trying to find the other numbers used in the formula. Thank you for your help. -- David "Mike Middleton" wrote: David - You could use =RSQ(LN(known_y's),known_x's). Or, you could use the array-entered LOGEST function suggested by Jerry W. Lewis. ISBN-10: 0534402933 ISBN-13: 9780534402938 - Mike "David" wrote in message ... Hi Mike, Should I be using a different function than RSQ to come up with the answer? If RSQ is for a linear function, what might I use for an exponential growth? I would be interested in your book, could you provide the ISBN. Thank you for your help. -- David "Mike Middleton" wrote: David - The chart uses an exponential function for the fit, and the chart trendline's R^2 value is based on that fit (which involves a log transformation of the Y data). The RSQ worksheet function is equivalent to using a linear function for the fit (involving no transformation). If you want more details about the differences, let me know, and I can send you some excerpts from my book "Data Analysis Using Microsoft Excel: Updated for Office XP." - Mike http://www.MikeMiddleton.com "David" wrote in message ... Hi Group, I am in "Microsoft Excel Data Analysis and Business Modeling." Chapter 42, pg 342-343 and put the R squared value on a chart, which equals .9828. Then i went to the functions and did RSQ, thinking this would give me the same answer, but it did not. Maybe I am using the wrong function, although it indicates that is give the R squared value, but the anser I get is .8386 The data: Year Sales 1 70 2 183 3 340 4 649 5 1243 6 1979 7 4096 8 6440 9 8459 10 12154 The graph gives one answer, (Format options on the trend line, "display R squared value on chart") = .9828, but the RSQ function gives .8386. -- David |
R squared
Thanks. Mike's stuff helped me a lot too. I am going to try and get a look at
his book too. -- David "MartinW" wrote: Hi David, I think you will find this link very handy. I know I did (do!) http://j-walk.com/ss///excel/tips/tip101.htm HTH Martin "David" wrote in message ... Thank you for your help. Looks like I will not get to try this until tomorrow. Thanks again, -- David "Mike Middleton" wrote: David - For an equation of the form Y = A*EXP(B*X), Excel's exponential trendline can be determined using these worksheet formulas: A =EXP(INTERCEPT(LN(known_y's),known_x's)) B =SLOPE(LN(known_y's),known_x's). - Mike "David" wrote in message ... Hi Mike, Thank you. This did find R2. Now I am trying to find the other numbers used in the formula. Thank you for your help. -- David "Mike Middleton" wrote: David - You could use =RSQ(LN(known_y's),known_x's). Or, you could use the array-entered LOGEST function suggested by Jerry W. Lewis. ISBN-10: 0534402933 ISBN-13: 9780534402938 - Mike "David" wrote in message ... Hi Mike, Should I be using a different function than RSQ to come up with the answer? If RSQ is for a linear function, what might I use for an exponential growth? I would be interested in your book, could you provide the ISBN. Thank you for your help. -- David "Mike Middleton" wrote: David - The chart uses an exponential function for the fit, and the chart trendline's R^2 value is based on that fit (which involves a log transformation of the Y data). The RSQ worksheet function is equivalent to using a linear function for the fit (involving no transformation). If you want more details about the differences, let me know, and I can send you some excerpts from my book "Data Analysis Using Microsoft Excel: Updated for Office XP." - Mike http://www.MikeMiddleton.com "David" wrote in message ... Hi Group, I am in "Microsoft Excel Data Analysis and Business Modeling." Chapter 42, pg 342-343 and put the R squared value on a chart, which equals .9828. Then i went to the functions and did RSQ, thinking this would give me the same answer, but it did not. Maybe I am using the wrong function, although it indicates that is give the R squared value, but the anser I get is .8386 The data: Year Sales 1 70 2 183 3 340 4 649 5 1243 6 1979 7 4096 8 6440 9 8459 10 12154 The graph gives one answer, (Format options on the trend line, "display R squared value on chart") = .9828, but the RSQ function gives .8386. -- David |
| All times are GMT +1. The time now is 08:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com