(Msg. 1) Posted: Wed Jul 30, 2008 6:40 pm
Post subject: Getting Trend Line Info from Chart Archived from groups: microsoft>public>excel>charting (more info?)
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:
(Msg. 2) Posted: Wed Jul 30, 2008 6:40 pm
Post subject: Re: Getting Trend Line Info from Chart [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
"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.aspx/excel-chart/200807/1 >
(Msg. 3) Posted: Wed Jul 30, 2008 8:01 pm
Post subject: Re: Getting Trend Line Info from Chart [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
(Msg. 4) Posted: Mon Aug 04, 2008 11:05 pm
Post subject: Re: Getting Trend Line Info from Chart [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
(Msg. 5) Posted: Tue Aug 05, 2008 11:36 am
Post subject: Re: Getting Trend Line Info from Chart [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Carrie -
Using Excel 2003 SP3, I get the same results as Power trendline when I use
these worksheet formulas:
"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.aspx/excel-chart/200808/1 >
(Msg. 6) Posted: Tue Aug 05, 2008 11:38 am
Post subject: Re: Getting Trend Line Info from Chart [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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,...
"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.aspx/excel-chart/200808/1 >
(Msg. 7) Posted: Tue Aug 05, 2008 6:54 pm
Post subject: Re: Getting Trend Line Info from Chart [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
(Msg. 8) Posted: Tue Aug 05, 2008 7:46 pm
Post subject: Re: Getting Trend Line Info from Chart [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
All times are: Eastern Time (US & Canada) (change) Goto page 1, 2
Page 1 of 2
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office