Conditional Formatting: what do I do wrong?

[Hans Troost]

Dear reader,

I made a spreadsheet with some measurement results. All measurements have an maximum acceptable value or a minimum acceptable value, some have both (so a range).
I added conditional formatting to get red coloured values of they are outside this range. The problem arises for those cases where the min. valu or the max. value is missing (and doesn't exist in reality).

I tried to capture that in the formula for the conditional formatting:

This is an image of the sheet (dates in Dutch, since I'm living in The Netherlands)

The conditional formatting is for an extended range of cells (for the future, possibly more measurements and for sure more dates:

and - the formula I used is this one:

intending that: if the value is higher then Max and the Max is given (not empty) then format red, OR the value is lower then Min. and Min. is not empty.
In text the fomula is:

=OR(AND(D2>C2;C2<>"");(AND(D2<b2;b2<>""))), where Column B possibly contains the Min value and Colum C the Max value.
</b2;b2<>​

It seems to work well in the first rows, but fails in some cases, highlighted in the red rectangle in the sheet. Rows for M-10 and M11

So... I did do something wrong. Can anybody help me out?

To enable you to check it yourself and play with it, please download the DropBox-copy: https://www.dropbox.com/s/wf2d20tjkzs4tq7/4MrExcel.xlsx?dl=0

Thanks in advance for any help,

Hans Troost

 

[AlanY]

try this

=OR(AND(D2>$C2,$C2<>0),(AND(D2<$B2,$B2<>0)))

 

[Hans Troost]

Solved: Conditional Formatting: what do I do wrong?

Thanks, that solves the problem!

 

[AntMac]

I just used this formula as a test where B36 is the value being tested. C is the minimum and D is the Maximum.

=AND(ISBLANK($C$36),$B$36<$D$36)