Formula based on min & max from other cells

[bigpaul706]

I am trying to use conditional formatting in a table based on the minimum and maximum values entered in other cells.

Based on the picture file I have attached, for row 2 I am using the formula =OR($D2>$C2,$D2<$B2)

If there are values in both columns (as per row 3) this formula works, but if the is nothing entered in the maximum column, the D column still turns red even when the figure is above the minimum.

I need this formula to work even if one of the columns is left blank

Can anyone advise me on how to fix this?

element columns.jpg

[bebo021999]

Just give C2 a big value if it is blank:

=OR($D2>IF($C2=0,10^10,$C2),$D2<$B2)

[bigpaul706]

Yes I thought of that, but unfortunately this is an excel table in a word template that goes to our customers and does need to match the requirements of the specification involved, which doesn't always have a min & a max so I need to find a way to work around this without entering random values.

[bebo021999]

So do you expect 1.5 to be FALSE and not highlight if MIN is 1 and no MAX? that my solution did.

[bigpaul706]

I see what you mean now, thanks for that. Yes that fixed that problem but caused another one. there are also occasions where there are only maximums and no value in the minimum column. This new formula now doesn't highlight if the figure is above the maximum when there is no minimum stated?