R squared

[David]

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.

 

[driller]

maybe some engineers in the club can join your query...
looking for the meaning of R square...

regards,
driller

*****
- dive with Jonathan Seagull

 

[Mike Middleton]

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]

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.

 

[Jerry W. Lewis]

LINEST(,,,TRUE) and LOGEST(,,,TRUE) provide the R^2 (3rd row, first column of
output) for the specified model.

Jerry

 

[David]

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.

 

[Mike Middleton]

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]

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.

 

[Mike Middleton]

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]

Thank you for your help. Looks like I will not get to try this until tomorrow.

Thanks again,

 

[MartinW]

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]

Thanks. Mike's stuff helped me a lot too. I am going to try and get a look at
his book too.