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

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

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

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

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

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

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

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

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

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

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

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