SharePoint Validation Formula Tip – Don't Use IF!

I often see questions about SharePoint validation formulas in the online forums that include IF statements to return True or False. Something like this:

    =IF(  someCondition , True, False  )
    =IF( Amount > 100, True, False )
    =IF( AND( State = "OH", Amount>100 ), True, False )

The IF is simply not needed!

Simply replace this:
    =IF( Amount > 100, True, False )
With this:
    =Amount > 100

It's either greater than, or it is not. The greater than test returns a True or False all by itself. For that matter, the expression in the first parameter of an IF statement must return True or False!

Here's what it looks like in both Classic and Modern UI.

What if you need to reverse the True and the False result?

Use the NOT function to reverse the True/False value.
    =NOT( Amount > 100 )

ANDs and ORs Return True or False

Simply replace this:
    =IF( AND( State = "OH", Amount>100 ), True, False )
With this:
    =AND( State = "OH", Amount>100 )

Two more examples…

If the state must be within the tri-state area you could write:
    =IF( State="OH", True, IF( State="KY", True", IF( State="IN",True, False ) ) )
Or you could just write:
    =OR( State="OH", State="KY", State="IN" )

While there are examples that require IF, you can solve most validations with a simple comparison, or the with the use of AND, OR and NOT.

    =IF( AND( Amount>100, IF( State="OH", True, IF( State="KY", True", IF( State="IN",True, False ) ) ) ), True, False)

Yup… I have seen those. And, it can be replaced with:
    =AND( Amount>100, OR( State="OH", State="KY", State="IN" ) )


And… book plug here… I have a lot more on validation formulas in my book!