# Thread: Charting 'High/Low Reference Range' + Actual value (2000/SP3)

1. ## Charting 'High/Low Reference Range' + Actual value (2000/SP3)

I have a series of 9 blood measurements for a patient. I have reference values of the normal range (High &low) for each of the 9 measurements, and Actual values for this patient. I would like to plot the High/Low as a bar - say for Lo & Hi of 5 & 10 a column starting at Y = 5 up to Y = 10. I would then like to plot the patient's Actual value over the top of this bar, preferably as a cross in a different colour, so it can be seen how the patient's value relates to the reference values.

I've tried using a Stock chart, using High/Low and Close, but it links the 3 data points as one line so the distinction is lost. Then I tried using error bars, as per the 'Chart' worksheet in the attached spreadsheet, but this doesn't work either, or at least, I'm lacking the skill to make it do what I want!

Any help would be much appreciated [img]/forums/images/smilies/smile.gif[/img]

Theo

2. ## Re: Charting 'High/Low Reference Range' + Actual value (2000/SP3)

If I understand what you are asking, the error bars should work.
These error ranges listed for "+" and "-" are the amounts to add and subtract from the value

If column C is the MIN values then you need to make a column of the D-C values and put this range in the "-" part.
If Col E is the max, then make a col of E-D and put this range in the "+" part.

Steve

3. ## Re: Charting 'High/Low Reference Range' + Actual value (2000/SP3)

Theo,

I tried using stock charts to display similar data. Whilst it was possible I didn't find it the simplest way of getting the desired result.

Atached is a sample chart that I have prepared (with random data) using the method I decided was quickest and allowed for the best presentation of the source data. I achieve the result by:

1. Using the 'Low' value and a derived value for 'Normal Spread', which is 'High' - 'Low'
2. These are displayed as a stacked column chart
3. The 'Low' column is given no border or area format and the series name is removed from the legend, this gives the impression that the 'Normal Spread' column is floating.
4. Finally the 'Patient' readings are displayed using a line chart.

Hope this is of use.

4. ## Re: Charting 'High/Low Reference Range' + Actual value (2000/SP3)

Thanks to you both.

Roger, your solution is superb. It's just what I was looking for, thank you!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•