Out of Range Data

[PTaz]

Hi

I am storing data (pulse rate) and displaying it in a line chart over a
period of time. I can set this up with no problems.

I have set ranges for minimum and maximum values (no ones pulse should be
below 40 or above 100 for example) I have set my Y axis to these values. and
My X axis is my dates.

The problem i have is that sometimes the value drops below or goes above the
set ranges. I am looking for a way to highlight these values on the chart.

Any help or advice would be appreciated.

Thanks
Phil

 

[David Biddulph]

The best bet is to use a formula put these values in a separate range, and
plot that range as a separate series.
e.g. =IF(A2>max_limit,A2,NA())

 

[PTaz]

Thanks David

Not sure how this will work. the values are supplied to me and i am trying
to show the trend over time for others to view. I have to keep a static axis
to allow us to seperate the out of range values.

I was hoping to use something similar to the Error bars, just to highlight
if the value was above or below the normal.

Thanks for your help and time.
Phil

 

[Jon Peltier]

Here is an illustrated tutorial which is similar to David's suggestion:

http://peltiertech.com/Excel/Charts/FormatMinMax.html

Your example would have dates in column A and values in column B. Put the
max allowed into cell E2 and the min allowed into cell F2. The formulas in
C2 and D2 are:

=IF($B2>$E$2,$B2,NA())=IF($B2<$F$2,$B2,NA())Fill these down columns C and D,
then make a chart using column A as the X values and columns B through D as
Y values for three series.- Jon-------Jon Peltier, Microsoft Excel
MVPTutorials and Custom SolutionsPeltier Technical Services, Inc. -