Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
JohanD
 
Posts: n/a
Default Line chart with multiple scales/units along an axis...


Hi!

I have a rather complex chart that I need to create... hopefully
someone here can help me with this issue...
The problem is the following:

I have offers from vendors for two types of cables that I want to
compare to each other in a chart. One vendor offers the cable in
certain lenghts measured in feet and one vendor offers in (different)
lenghts measured in meters. What I want is plot the price/feet on the
Y-axis and have both a feet scale and a metric scale on the X-axis and
the scales are to be linear i.e. fixed intervals at say 0, 500, 1000,
1500... ft and the metric at 0, 500, 1000... meter etc.
There is not a lot of data, basically just this:

Vendor "A":
5000 ft, 8 USD/ft
25000 ft, 7 USD/ft
50000 ft, 6 USD/ft
etc.

Vendor "B":
300 m, 10 USD/ft
5000 m, 7 USD/ft
25000 m, 6 USD/ft
etc...

Plotting this with a line chart with data points marked is not a
problem but I can only get one unit on the X-axis where I would like to
have 2 scales with different units (ft and meters).

Even better would be to be able to have a third scale on the X-axis
that displays the number of units a certain lenght of cable is good for
(one unit = 110 ft of cable) so one can look at the chart and read that
"xxx meters/feet of cable from vendor A/B equals xxx number of finished
units and costs xxx USD/ft).

Whew... maybe too much details but PLEASE tell me this is possible to
do!:)

Any input into how to make such a chart is extremely welcome!

/Johan


--
JohanD
------------------------------------------------------------------------
JohanD's Profile: http://www.excelforum.com/member.php...o&userid=32573
View this thread: http://www.excelforum.com/showthread...hreadid=523733

  #2   Report Post  
Posted to microsoft.public.excel.charting
Jon Peltier
 
Posts: n/a
Default Line chart with multiple scales/units along an axis...

Johan -

Here's how to arrange the data, in four columns:

Vendor A Vendor B
5000 8 300 10
25000 7 5000 7
50000 6 25000 6

Make an XY chart (not a line chart) with the vendor A data (including the
first row with the blank and 'Vdr A' label). Copy the vendor B data (incl.
the first row with the blank and the 'Vdr B' label), select the chart, and
use Paste Special from the Edit manu to add the data as a new series. Double
click on the Vendor B series, and on the Axis tab, select Secondary. Go to
Chart Options on the Chart menu, and on the Axis tab, uncheck the secondary
Y axis but check the secondary X axis. Double click the bottom X axis, and
on the Scale tab choose appropriate values for the maximum and major units,
and make sure the Auto boxes in front of the input boxes are unchecked.
Double click on the top X axis, and on the Scale tab, enter an appropriate
value for the maximum scale, and for the maximum, enter the maximum of the
bottom scale times 0.3048, the conversion factor from feet to meters; again,
make sure the Auto boxes are unchecked.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

"JohanD" wrote in
message ...

Hi!

I have a rather complex chart that I need to create... hopefully
someone here can help me with this issue...
The problem is the following:

I have offers from vendors for two types of cables that I want to
compare to each other in a chart. One vendor offers the cable in
certain lenghts measured in feet and one vendor offers in (different)
lenghts measured in meters. What I want is plot the price/feet on the
Y-axis and have both a feet scale and a metric scale on the X-axis and
the scales are to be linear i.e. fixed intervals at say 0, 500, 1000,
1500... ft and the metric at 0, 500, 1000... meter etc.
There is not a lot of data, basically just this:

Vendor "A":
5000 ft, 8 USD/ft
25000 ft, 7 USD/ft
50000 ft, 6 USD/ft
etc.

Vendor "B":
300 m, 10 USD/ft
5000 m, 7 USD/ft
25000 m, 6 USD/ft
etc...

Plotting this with a line chart with data points marked is not a
problem but I can only get one unit on the X-axis where I would like to
have 2 scales with different units (ft and meters).

Even better would be to be able to have a third scale on the X-axis
that displays the number of units a certain lenght of cable is good for
(one unit = 110 ft of cable) so one can look at the chart and read that
"xxx meters/feet of cable from vendor A/B equals xxx number of finished
units and costs xxx USD/ft).

Whew... maybe too much details but PLEASE tell me this is possible to
do!:)

Any input into how to make such a chart is extremely welcome!

/Johan


--
JohanD
------------------------------------------------------------------------
JohanD's Profile:
http://www.excelforum.com/member.php...o&userid=32573
View this thread: http://www.excelforum.com/showthread...hreadid=523733



  #3   Report Post  
Posted to microsoft.public.excel.charting
JohanD
 
Posts: n/a
Default Line chart with multiple scales/units along an axis...


Thanks! That works great!

One question, is there any way to have both x-axis at the bottom of the
chart instead of one on top and one on the bottom?
I did not find any way of changing their position...

Follow up: if it's possible to have several axis at the same place
(i.e. if my question above is possibe) then I guess it should be
possible to have a tertiary axis (X-axis) also but if the X-axis can
only be put on top or on the bottom of the chart I guess a third x-axis
is out of the question?

Thanks,
Johan


--
JohanD
------------------------------------------------------------------------
JohanD's Profile: http://www.excelforum.com/member.php...o&userid=32573
View this thread: http://www.excelforum.com/showthread...hreadid=523733

  #4   Report Post  
Posted to microsoft.public.excel.charting
Jon Peltier
 
Posts: n/a
Default Line chart with multiple scales/units along an axis...

You cannot get both X axes right at the bottom, but you could offset one
somewhat by changing the Crosses At value. For example, you could have one
horizontal axis at Y=0 and the second at Y=1. Y=0 is easy. For Y=1, double
click the corresponding Y axis (primary or secondary, and do this before
unchecking the secondary Y axis, if you're placing the secondary X axis at
Y=1), and on the Scale tab, enter 1 in the Category/Value X Axis Crosses At
box.

For a trick to get a tertiary axis (it can be adapted to horizontal axes):

http://peltiertech.com/Excel/Charts/TertiaryAxis.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services - Tutorials and Custom Solutions -
http://PeltierTech.com/
2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______

"JohanD" wrote in
message ...

Thanks! That works great!

One question, is there any way to have both x-axis at the bottom of the
chart instead of one on top and one on the bottom?
I did not find any way of changing their position...

Follow up: if it's possible to have several axis at the same place
(i.e. if my question above is possibe) then I guess it should be
possible to have a tertiary axis (X-axis) also but if the X-axis can
only be put on top or on the bottom of the chart I guess a third x-axis
is out of the question?

Thanks,
Johan


--
JohanD
------------------------------------------------------------------------
JohanD's Profile:
http://www.excelforum.com/member.php...o&userid=32573
View this thread: http://www.excelforum.com/showthread...hreadid=523733



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Bar Chart To Line Chart for last 3 points only Big H Charts and Charting in Excel 1 October 13th 05 09:55 PM
Line chart for churn rates of many items over time Whoops Charts and Charting in Excel 3 August 25th 05 07:26 PM
How do I change x axis values in a line chart? Elizabeth Charts and Charting in Excel 1 March 29th 05 11:05 AM
Problems with X Axis for a Line Chart Shadowman13 Charts and Charting in Excel 3 February 18th 05 01:15 AM
pivot table multi line chart souris Charts and Charting in Excel 2 December 7th 04 04:56 AM


All times are GMT +1. The time now is 06:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"