Getting Trend Line Info from Chart
 

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200807/1

Getting Trend Line Info from Chart
 

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" <u34134@uwe wrote in message
news:87eedf02ece9e@uwe...
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200807/1

Getting Trend Line Info from Chart
 

Thanks!

Mike Middleton wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200807/1

Getting Trend Line Info from Chart
 

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?

Mike Middleton wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200808/1

Getting Trend Line Info from Chart
 

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


"Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message
news:88300cd362c45@uwe...
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?

Mike Middleton wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200808/1

Getting Trend Line Info from Chart
 

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



"Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message
news:88300cd362c45@uwe...
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?

Mike Middleton wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200808/1

Getting Trend Line Info from Chart
 

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!


Mike Middleton wrote:
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]
Thank you in advance for any asssistance.
Carrie

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200808/1

Getting Trend Line Info from Chart
 

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

Mike Middleton wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200808/1

Getting Trend Line Info from Chart
 

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


"Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message
news:883ae2ef08ffc@uwe...
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

Mike Middleton wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200808/1

Getting Trend Line Info from Chart
 

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.

Mike Middleton wrote:
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

--
Message posted via http://www.officekb.com

Getting Trend Line Info from Chart
 

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

Jerry

"Carrie_Loos via OfficeKB.com" wrote:

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.

Mike Middleton wrote:
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

--
Message posted via http://www.officekb.com

Getting Trend Line Info from Chart
 

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.

Jerry W. Lewis wrote:
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]
Thank you in advance for any asssistance.
Carrie

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...chart/200808/1