Line chart or scatter chart??

Hi Rmellison,

If thats the only way to do it, I'll have to try and fudge something
together. But if anybody knows a way to get increasing/decreasing values (eg.
60 to 100 to 60) on the x-axis of a scatter graph, it'd make things a whole
lot simpler...

OK, we just need to get creative. Firstly, you're going to want to use an XY
scatter chart, so we have to modify the X scale such that Excel actually plots
the range 60-140, but display 60-100-60 along the axis.

So the fake X scale is given by:
=A1 (for the top cells)
=200-A1 (for the bottom cells)

Plot each series using that scale to get the correct horizontal positioning of
your data points.

Now create a column of cells with the actual numbers that you want to display
along the x axis, e.g. 60, 70, 80, 90, 100, 90, 80, 70 60, with 10 alongside
them. Select those cells, copy them, click the chart, choose Paste Special and
say the the x axes values are in the first column, but don't replace existing
values. That should give you a horizontal line on the chart. Select it, change
the chart type to a line chart (Chart Chart Type Line) and elect to plot it
on the secondary axes (Double-click it Axis Secondary). Then use the chart
options to display the secondary X axis but not Y axis (Chart Chart Options
Axis).

Double-click the bottom axis, go to the scale tab and set the scale to go from
55 to 145 in steps of 5. That should give you the numbers 55-145 along the
bottom (from the XY chart) and 60-100-60 along the top (from the line chart),
with the numbers lining up correctly. Now double-click the bottom axis, go to
the patterns tab and choose not to display tick mark labels. Then double-click
the top axis, go to the patterns tab, choose to not display tick marks and
display the tick labels 'Low'.

Lastly, format the dummy series we used for the tick mark labels to have no line
style and no pattern.

It was a bit of work, but we're done!

Regards

Stephen Bullen
Microsoft MVP - Excel

Professional Excel Development
The most advanced Excel VBA book available
www.oaltd.co.uk/ProExcelDev