Getting Trend Line Info from Chart

[Carrie_Loos via OfficeKB.com]

Hi -

I am creating a manpower report and updating learning curve charts for a
decision making tool through VBA. The issue I am having is the trend line
(Power) in the charts gives me the output I need but I cannot figure out how
to pull the values I want into the spreadsheet. I have tried a couple of
functions but have come up empty handed. The RSQ function is not multilinear.
Does anyone know the formula for a power trend line? For example out of 100
samples I get the following info:

y = 1.5326x0.0657
R2 = 0.5744

Thank you in advance for any asssistance.
Carrie

 

[Mike Middleton]

Carrie -

For formulas, see John Walkenbach's summary:

http://www.j-walk.com/ss/excel/tips/tip101.htm

His formulas for the Power trendline use the array-entered LINEST function,
but you could use the standard SLOPE and INTERCEPT functions, instead.

For the Power trendline, Excel transforms both the Y and X values before
using a linear fit, so the RSQ results are for fitted Log(Y).

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[Carrie_Loos via OfficeKB.com]

Thanks!

Carrie -

For formulas, see John Walkenbach's summary:

http://www.j-walk.com/ss/excel/tips/tip101.htm

His formulas for the Power trendline use the array-entered LINEST function,
but you could use the standard SLOPE and INTERCEPT functions, instead.

For the Power trendline, Excel transforms both the Y and X values before
using a linear fit, so the RSQ results are for fitted Log(Y).

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

Hi -

[quoted text clipped - 14 lines]

Thank you in advance for any asssistance.
Carrie

 

[Carrie_Loos via OfficeKB.com]

OK - Still stuck - tried several LINEST formuals and CORREL(LN(y),x) none of
which gave me the same answer. Do you know a formula that will work?

Carrie -

For formulas, see John Walkenbach's summary:

http://www.j-walk.com/ss/excel/tips/tip101.htm

His formulas for the Power trendline use the array-entered LINEST function,
but you could use the standard SLOPE and INTERCEPT functions, instead.

For the Power trendline, Excel transforms both the Y and X values before
using a linear fit, so the RSQ results are for fitted Log(Y).

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

Hi -

[quoted text clipped - 14 lines]

Thank you in advance for any asssistance.
Carrie

 

[Mike Middleton]

Carrie -

Using Excel 2003 SP3, I get the same results as Power trendline when I use
these worksheet formulas:

=SLOPE(LN(Yrange),LN(Xrange))
=EXP(INTERCEPT(LN(Yrange),LN(Xrange)))
=RSQ(LN(Yrange),LN(Xrange))

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

OK - Still stuck - tried several LINEST formuals and CORREL(LN(y),x) none
of
which gave me the same answer. Do you know a formula that will work?

Carrie -

For formulas, see John Walkenbach's summary:

http://www.j-walk.com/ss/excel/tips/tip101.htm

His formulas for the Power trendline use the array-entered LINEST
function,
but you could use the standard SLOPE and INTERCEPT functions, instead.

For the Power trendline, Excel transforms both the Y and X values before
using a linear fit, so the RSQ results are for fitted Log(Y).

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

Hi -

[quoted text clipped - 14 lines]

Thank you in advance for any asssistance.
Carrie

 

[Mike Middleton]

Carrie -

Be sure to use an XY (Scatter) chart type.

If you use any other chart type, e.g., Line, the trendline feature treats
the X values as integers 1,2,3,...

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

OK - Still stuck - tried several LINEST formuals and CORREL(LN(y),x) none
of
which gave me the same answer. Do you know a formula that will work?

Carrie -

For formulas, see John Walkenbach's summary:

http://www.j-walk.com/ss/excel/tips/tip101.htm

His formulas for the Power trendline use the array-entered LINEST
function,
but you could use the standard SLOPE and INTERCEPT functions, instead.

For the Power trendline, Excel transforms both the Y and X values before
using a linear fit, so the RSQ results are for fitted Log(Y).

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

Hi -

[quoted text clipped - 14 lines]

Thank you in advance for any asssistance.
Carrie

 

[Carrie_Loos via OfficeKB.com]

Thank you - I am using the Scatter chart. I was only having difficulty with
the R2 formula, it's working great now with what you gave me!

Carrie -

Be sure to use an XY (Scatter) chart type.

If you use any other chart type, e.g., Line, the trendline feature treats
the X values as integers 1,2,3,...

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

OK - Still stuck - tried several LINEST formuals and CORREL(LN(y),x) none
of

[quoted text clipped - 22 lines]

 

[Carrie_Loos via OfficeKB.com]

By the way, can you use a dynamic range name for the Xrange & Yrange? I can't
seem to get it to work. I keep getting a #NAME? error

Carrie -

Using Excel 2003 SP3, I get the same results as Power trendline when I use
these worksheet formulas:

=SLOPE(LN(Yrange),LN(Xrange))
=EXP(INTERCEPT(LN(Yrange),LN(Xrange)))
=RSQ(LN(Yrange),LN(Xrange))

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

OK - Still stuck - tried several LINEST formuals and CORREL(LN(y),x) none
of

[quoted text clipped - 22 lines]

 

[Mike Middleton]

Carrie -

You can use defined names Xrange and Yrange to refer to the numeric data
ranges on your worksheet. And if you change the Refers To range of both
defined names, the formulas will update. In Excel 2003, choose Insert | Name
| Define.

- Mike

By the way, can you use a dynamic range name for the Xrange & Yrange? I
can't
seem to get it to work. I keep getting a #NAME? error

Carrie -

Using Excel 2003 SP3, I get the same results as Power trendline when I use
these worksheet formulas:

=SLOPE(LN(Yrange),LN(Xrange))
=EXP(INTERCEPT(LN(Yrange),LN(Xrange)))
=RSQ(LN(Yrange),LN(Xrange))

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

OK - Still stuck - tried several LINEST formuals and CORREL(LN(y),x)
none
of

[quoted text clipped - 22 lines]

Thank you in advance for any asssistance.
Carrie

 

[Carrie_Loos via OfficeKB.com]

Thanks and sorry I should have been clear in defining my issues. I have
reposted the issue under Dynamic Range Name in Functions. It's true the
defined names work as long as I have a set range but the values change and if
there is a blank then I get the #VALUE! error. So I am trying to get a
dynamic range to work but so far I have not been successful.

Carrie -

You can use defined names Xrange and Yrange to refer to the numeric data
ranges on your worksheet. And if you change the Refers To range of both
defined names, the formulas will update. In Excel 2003, choose Insert | Name
| Define.

- Mike

By the way, can you use a dynamic range name for the Xrange & Yrange? I
can't

[quoted text clipped - 19 lines]

 

[Jerry W. Lewis]

Instead of
LN(range)
use
If(ISNUMBER(range),LN(range))
and array enter the formula.

Jerry

Thanks and sorry I should have been clear in defining my issues. I have
reposted the issue under Dynamic Range Name in Functions. It's true the
defined names work as long as I have a set range but the values change and if
there is a blank then I get the #VALUE! error. So I am trying to get a
dynamic range to work but so far I have not been successful.

Carrie -

You can use defined names Xrange and Yrange to refer to the numeric data
ranges on your worksheet. And if you change the Refers To range of both
defined names, the formulas will update. In Excel 2003, choose Insert | Name
| Define.

- Mike

By the way, can you use a dynamic range name for the Xrange & Yrange? I
can't

[quoted text clipped - 19 lines]

Thank you in advance for any asssistance.
Carrie

 

[Carrie_Loos via OfficeKB.com]

Not sure if I did this correctly but it came up with a zero value =IF
(ISNUMBER($E$4:$E$103),LN($E$4:$E$103)) - entered as an array formula,
correct? This is the X range. It represents the number of units in sequential
order. So that the range is a possible 100 vaules and I currently have 1 - 82
in the range. The rest or the range are true blank cells.

Instead of
LN(range)
use
If(ISNUMBER(range),LN(range))
and array enter the formula.

Jerry

Thanks and sorry I should have been clear in defining my issues. I have
reposted the issue under Dynamic Range Name in Functions. It's true the

[quoted text clipped - 16 lines]