highlight high and low ranges in chart

[pennycents]

Trying to chart the acutal readings of blood sugar on a chart on which the
normal range say between 90 and 120 are highlighted on the chart. If I try
to insert a text box and if a reading falls in the normal range, the reading
is hidden. I tried to group the plotted readings and the text box to send
the text box behind the readings but could not.

 

[Dave Curtis]

Hi,
One of many possible ways is to use a helper column. Suppose your
dates/times are in A2:A20, and your readings iare in B2:B20.
In C2, enter =IF(AND(B2>=90,B2<=120),NA(),B2)
and copy down to C20.
This will give a seriescontaining only those readings below 90 or above 120.
The #N/A errors do not appear on the chart.
Then plot a x-y chart using A2:A20 and C2:C20, and you should have what you
want.
Alternatively, create a chart using all three columns and you'll see that
the readings within range are one colour, while those outside the range are
plotted on top in another colour.
Format to smarten the whole thing up, and you're finished, hopefully.
If you need lines to indicate the "LIMITS" at 90 and 120,
add two more series to the chart(put 90 in D2 and 120 in E2). Then add
positive X error bars for the appropriate number of time units.Format as
required.

Good luck.

Dave

url:http://www.ureader.com/msg/10296609.aspx