Thread: Medical Chart Issues

I have a simple 3-column spreadsheet of medical data. Column A is consecutive dates. Column B is a test reading which is taken twice a week, so that there are days with no value in this column. Column C is the daily dose of medicine administered.

I need to chart Column B only (the test results with skipped data points) against the dates in column A. There is a desired range of results we want the patient to be in. So my questions are two:

1) How can I get a line chart in Excel to draw lines between data points when there are those skipped data points?

2) How can I have a certain range in the plot area have a different color? Specifically, results range between 0.0 and about 5.0. The desired range is 2.0 to 3.0 and I'd like that part of the chart to have a different background color to indicate it's the desired target range.

Can I do this without complex VBA?

TIA,
Bill

Can you post a small example file?

Which xl version?

Thanks, Andy. I'm running Excel 2003.

A small sample spreadsheet is attached.

See attached,

To get the line select chart,
Tools > Options > Chart > Plot empty cells > Interpolate

to add background shading,
Add 3 new series E2:F2 E3:F3 E4:F4
change to secondary axis
change to stacked column
Right click chart > Chart Options > Axes
Remove Secondary Y axis
Apply Secondry X axis
change gap width to 0
format to required colours
Secondary X axis remove Tick labels

Thanks, Andy!

I got as far as the step to change to secondary axis. How do I do that?

To apply the seconday axis you can use

Right click > Chart Options > Axes > Secondary Axis.
Once the secondary axis is displayed you can double click it in order to display the Format dialog

Thanks!! I'll go and try that.
Bill

Hi, Andy,

I don't find any secondary axis options in the Chart Options | Axes dialog. Is that in newre versions of Excel? I'm using Excel 2003.

Excel help says to select the data series in the embedded chart and then from the menu Format | Selected Data Series | Secondary Axis --- but I'm having trouble selecting the data series because there's no data there. I tried selecting it from the Source Data Series dialog but it didn't work.

Thanks for any further advice!
Bill

The description was for xl2003.

This page shows you the Chart Options dialog.
http://www.andypope.info/tips/tip008.htm

If the series has no data you could try changing one of the cell to have a value whilst formatting the chart.