L
LeAnne
Thought I'd share this with the group. It's really just a variation of a
technique worked out by Jon Peltier. Note: developed using A97, untested
in later versions.
- Create xy scatter chart.
- Enter your target threshold values in an adjacent range of cells like so:
[blank] Target
0 9.1
1 9.1
- Select the chart, Chart>Source Data, Series tab. Click Add button to
create a new series (name doesn't really matter). Set x values to the
range in the <blank> column & the y values to the values in the Target
column. Click OK. Look carefully for the newly added series; it can be
hard to spot on a very busy chart.
- Select the series (once you find it!), Chart>Chart Type, change to xy
scatter with data points connected by lines without markers.
- Select the series, Format>Selected Data Series, Axis tab. Uncheck
primary axis (if needed) & check secondary axis (if needed).
- Select the chart, Chart>Chart Options, Axes tab. Ignore primary axis
box. Check secondary x axis & uncheck secondary y axis.
- Select the secondary x-axis (top of chart), Format>Selected Axis,
Scale tab. Set Min=0, Max=1, ignore Major/minor units, and set Value
axis crosses at = 1.
- Then click on the Patterns tab and select "None" for major and minor
tick marks & labels.
Now, this will work fine "as is" if you only want 1 horizontal line. But
suppose you want to add a second horizontal line to the same chart -
say, to show upper and lower target values? Here's how to do that:
- First, select the first horizontal line you added, Chart>Chart Type,
and change it to line without markers.
- Select the series, Format>Selected Data Series, Axis tab, and make
sure secondary Axes is (still) checked.
- Select the chart, Chart>Chart Options, Axes tab, and make sure the
secondary x axis is (still) checked & the secondary y axis is (still)
unchecked.
- Select the secondary x-axis, Format>Selected Axis, Scale tab. Uncheck
Value (Y) axis crosses between categories.
- Then click on the Patterns tab and select "None" for major and minor
tick marks & labels.
-Now adding the second horizontal line is quite easy: Select chart,
Chart>Source Data, click the Add button to add a new series (again, name
doesn't matter). You now have only to enter the cells containing the
values corresponding to your second target thresholds as the Values.
Delete anything that Excel tries to throw into the "Second category (X)
axis labels field."
- Bingo! You're done.
Hoping that some may find this helpful,
LeAnne A.
technique worked out by Jon Peltier. Note: developed using A97, untested
in later versions.
- Create xy scatter chart.
- Enter your target threshold values in an adjacent range of cells like so:
[blank] Target
0 9.1
1 9.1
- Select the chart, Chart>Source Data, Series tab. Click Add button to
create a new series (name doesn't really matter). Set x values to the
range in the <blank> column & the y values to the values in the Target
column. Click OK. Look carefully for the newly added series; it can be
hard to spot on a very busy chart.
- Select the series (once you find it!), Chart>Chart Type, change to xy
scatter with data points connected by lines without markers.
- Select the series, Format>Selected Data Series, Axis tab. Uncheck
primary axis (if needed) & check secondary axis (if needed).
- Select the chart, Chart>Chart Options, Axes tab. Ignore primary axis
box. Check secondary x axis & uncheck secondary y axis.
- Select the secondary x-axis (top of chart), Format>Selected Axis,
Scale tab. Set Min=0, Max=1, ignore Major/minor units, and set Value
axis crosses at = 1.
- Then click on the Patterns tab and select "None" for major and minor
tick marks & labels.
Now, this will work fine "as is" if you only want 1 horizontal line. But
suppose you want to add a second horizontal line to the same chart -
say, to show upper and lower target values? Here's how to do that:
- First, select the first horizontal line you added, Chart>Chart Type,
and change it to line without markers.
- Select the series, Format>Selected Data Series, Axis tab, and make
sure secondary Axes is (still) checked.
- Select the chart, Chart>Chart Options, Axes tab, and make sure the
secondary x axis is (still) checked & the secondary y axis is (still)
unchecked.
- Select the secondary x-axis, Format>Selected Axis, Scale tab. Uncheck
Value (Y) axis crosses between categories.
- Then click on the Patterns tab and select "None" for major and minor
tick marks & labels.
-Now adding the second horizontal line is quite easy: Select chart,
Chart>Source Data, click the Add button to add a new series (again, name
doesn't matter). You now have only to enter the cells containing the
values corresponding to your second target thresholds as the Values.
Delete anything that Excel tries to throw into the "Second category (X)
axis labels field."
- Bingo! You're done.
Hoping that some may find this helpful,
LeAnne A.