How do I show slope on a chart?

[Need Help on axis problem in a chart]

I have a simple plot with two points only, and added a trendline which fits
with the plotted line. I really need to show the slope of the trend line on
the plot. But I dont want to show the equation or anything else.

I am using a plot which should calculated and plot by itself with the change
of inputs in the table(somewhere in the page), and need to show that slope
value on the plot too.

I can calculate slope in a cell somewhere on the sheet. Is there any way to
show the value (calculated somwhere on the sheet) on the plot beside my
plotted line.

Either way would work. But I prefer to do it from the trendline.
please help.

 

[Andy Pope]

Hi,

Use the text label provided by the Trendline equation. Once applied you can
then link it to a cell.
select the label and then in the formula bar type = and then click the
required cell. This will
create the required cell reference.

Cheers
Andy

 

[Need Help on axis problem in a chart]

No, It didnt work,
I tried typing = then a cell destination.
it just showed me what I typed. It did not take the vales from the cell.

or IF I select the equation level and go to formula bar, the equation level
just disappears.

Is there any other way to get it to work ??

Thanks in advance

 

[Andy Pope]

Have a look here for details.
http://www.andypope.info/tips/tip001.htm

I have tried it and it does work.

Cheers
Andy

 

[MartinW]

Hey, that's a very neat trick Andy, thank you!!

For the benefit of the OP I'll explain what I did.

Data setup:
X values in A1:A5
Y values in B1:B5
In D7 put =SLOPE(B1:B5,A1:A5)
In E7 put ="Slope = "&D7

Then click on the equation in the chart and the handles
will show. Then I put this in the formula bar =Sheet1!$E$7
and hit enter. Jobs done!

I then took it further and added this
In D8 put =INTERCEPT(B1:B5,A1:A5)

Then changed E7 to this
="Gradient = "&D7&CHAR(10)&"Y Intercept = "&D8
Which is much neater than the standard equation.

I took it further again and put this in E7
="Trendline equation is y = mx+c where"&CHAR(10)&"m = "&
SLOPE(B1:B5,A1:A5)&CHAR(10)&"c = "&INTERCEPT(B1:B5,A1:A5)

Which is interesting but not very practical, far better to
put the formalae in other cells then just reference
those cells like this,
="Trendline equation is y = mx+c where"&CHAR(10)&"m = "&D7&CHAR(10)&"c =
"&D8

Once again thank you Andy, up until now I've been using
a text box to display this, which works but is manual and as
such I sometimes forget to change the text box after a source
data change.

Regards
Martin

 

[Andy Pope]

Hi,

Thanks for posting

Cheers
Andy

Hey, that's a very neat trick Andy, thank you!!

For the benefit of the OP I'll explain what I did.

Data setup:
X values in A1:A5
Y values in B1:B5
In D7 put =SLOPE(B1:B5,A1:A5)
In E7 put ="Slope = "&D7

Then click on the equation in the chart and the handles
will show. Then I put this in the formula bar =Sheet1!$E$7
and hit enter. Jobs done!

I then took it further and added this
In D8 put =INTERCEPT(B1:B5,A1:A5)

Then changed E7 to this
="Gradient = "&D7&CHAR(10)&"Y Intercept = "&D8
Which is much neater than the standard equation.

I took it further again and put this in E7
="Trendline equation is y = mx+c where"&CHAR(10)&"m = "&
SLOPE(B1:B5,A1:A5)&CHAR(10)&"c = "&INTERCEPT(B1:B5,A1:A5)

Which is interesting but not very practical, far better to
put the formalae in other cells then just reference
those cells like this,
="Trendline equation is y = mx+c where"&CHAR(10)&"m = "&D7&CHAR(10)&"c =
"&D8

Once again thank you Andy, up until now I've been using
a text box to display this, which works but is manual and as
such I sometimes forget to change the text box after a source
data change.

Regards
Martin

 

[Jon Peltier]

If you only have two points, why bother with a trendline? Format the series
to show a line connecting the markers, then use Andy's and Martin's
suggestions to apply a label to one endpoint with the slope.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Need Help on axis problem in a chart"