Excel Custom Data Validation Allows Invalid Values

0
votes

I am trying to write a custom validation formula to require the following conditions for Cell O22 on a spreadsheet:

  • Value must be a number
  • Value cannot be zero
  • Value must be greater than or equal to 0.001
  • Value must be greater than the cell to the right

In the example, O22 is the gross weight for an object and P22 is the net weight, so both will be numbers.

So I plug in the following formula to a custom data validation box:

=AND(ISNUMBER(O22), O22<>0, O22>0.001, O22>P22)

However, when I go to enter in an invalid value such as the letter "A", no error message appears. I can put the exact same formula into another cell and it will evaluate as false, because "A" is not a number.

After building the formula out one condition at a time, everything works correctly until I add the O22>P22 test. This is when it starts accepting any value without error. Can anyone suggest a workaround for this?

1
excelexcel-formulavalidation
I used your validation formula and it correctly prevented me from entering invalid data. - xidgel
It might help to try: =IF(ISNUMBER(O22),AND(O22<>0, O22>0.001, O22>P22),FALSE) to prevent numerical comparisons on non-numerical data. - xidgel
Yeah this seems to work fine. Also the 022<>0 is redundant as you also state 022>0.001 which will test false if the value is zero. I would try @xidgel answer. That way you will know if the value is being stored an non-numerical which it probably is if its not working for you. - bbishopca
I tried @xidgel answer, but if P22 is blank, I can enter any value I want into O22 without any complaint from Excel. I tried adding P22="" to the AND function tests as well as trying this on a blank spreadsheet in case something was corrupted with the original, but no change. If P22 is empty, I can enter whatever value I want into O22. If P22 has a value, O22's data validation will work as expected. - Aerogems

1 Answers

0
votes

You need to uncheck the Ignore Blank check box in the Data Validation dialog box. If Ignore Blank is checked, no data will be validated as long as a cell in your validation formula (in this case P22) is blank.

Once Ignore Blank is unchecked, any formula that returns FALSE will be considered Invalid Data.