Excel Data Validation multiple conditions not working …?

Question

I would like to do Data Validation on Cell E10 as follows:

1. F10 is blank (F10 = "")
2. E10 (self) is not greater than the maximum of range C9 to C100 (E10 <= max(C9:C100)
3. E10 (self) is not equal to B10 (E10 <> B10)

So, one would think that the correct formula for the custom data validation is:

=IF(AND(F10 = "", E10 <= MAX(C9:C100), E10 <> B10),1,0)

or

=(F10 = "")*(E10 <= MAX(C9:C100))*(E10 <> B10)

However, neither of these actually work in my case. I've extensively tested: each condition works fine on its own. (2) and (3) can be successfully combined as well. But, (2) and (3) are ignored the moment they are combined with (1), in which case only (1) is respected.

Can anyone help me to get Excel to respect all three the conditions?

Answer 1

This will work:

=AND((F10 = ""),(E10 <= MAX(C9:C100)),(E10 <> B10))

but you must clear the ignore blanks checkbox!