Tolerances in a temperature chart - conditional formatting - help!

[Rezendes]

I am testing 10 thermometers and using Excel for my data. There is a +/-3°
allowable tolerance. Column A is my set scale (-5°C to +5°C) and columns B
through K are my result figures for these devices. I'd like to conditionally
format the cells of the results that are too low (>-3) blue and if they are
too high (>+3) format the red.

Thank you for your help in advance!

 

[David Biddulph]

What is your question? Have you looked at help for conditional formatting?
What don't you understand?

 

[Bernard Liengme]

My first reference value is in A2 (that has -5)
I selected B2:K11 and in the Conditional Formatting dialog used
Formula Is =B2-$A2>3 and set font colour to red
For the second one I used
Formula Is =$A2-B2>3 and set font colour to blue
You must have numbers in all cells
but you could use Custom Format of: 0 "°C" to display -5 as -5°C
best wishes

 

[Rezendes]

Thank you Bernard!

If you would be so kind, I have a follow up question because I am unable to
create one additional CF rule. For reference, my actual tolerance is +- 1°C.
With your help, my spreadsheet now shows temps that are too low in blue, too
high in red - thank you!

I'd like to add one more CF rule where the cell values X (when compared to
column A) are yellow under these conditions (both positive and negative -
perhaps using absolute value?): 1>X>.5

My trial and error attempts have all been errors to this point!
Thank you again for your invaluable assistance!

 

[Bernard Liengme]

I think this is what you want
=AND(ABS($A2-B2)>=0.5,ABS($A2-B2)<=1)

Be careful with colours. Many people (men) are red colour blind and yellow
is really hard to read
I experimented: gave all the cells a light grey fill and in conditional
formatting I used coloured borders rather than font colour. Looked quite
nice!

best wishes

 

[Rezendes]

Bernard,

Thank you for the advice on color usage. My spreadsheet is a couple of
printed pages long. The effect I'm after with the colors is to easily
identify over and under spec conditions (+-1°C) as well as "warning sign"
conditions (=- .5° to +-.9°). More than 95% of the values are within spec so
there is no color there. With your help, the out of spec cells stand out
quite nicely and will help the engineers correct the calibration curve easily.

The yellow formula doesn't seem to be working as expected - I have temp
values that are less than +-.5 that are getting the yellow when that should
not be the case and values that should be yellow are not. I highlighted my
entire data set, added the yellow rule as you wrote it. I'm simply not sure
why it doesn't perform correctly.

Any additional assistance would be greatly appreciated! Thank you a thousand
times over for your help!

 

[Bernard Liengme]

Maybe its time for us to work off-group. Send me a sample file; just remove
TRUENORTH. for the address showing in this message. I have a working file
that does what I think you want but I may have missed something.
best wsihes