Using IF & OR Instead Of Embedded IFs In #Excel

Here is how you can use the OR function with an IF statement instead of multiple embedded IFs in Excel:

You can download the file here to follow along. If you get a preview, look for the download arrow in the upper right hand corner.

We have a list of locations and their sales. I’ve targeted 3 locations to check to see if any of them met their sales goals. If any did, I want my formula to return a “YES”, if not, then “NO”:

ifor1

As you can see by the Test indicators in column “F”, two of the locations did meet their target, so I should get a “YES” in my formula.

First we’ll construct a formula using embedded IF statements. That formula will be:

=IF(B2>E2,”YES”,IF(B6>E6,”YES”,IF(B9>E9,”YES”,”NO”)))

ifor2

As you can see, two of the 3 locations did meet their targets, so I get a “YES” with our embedded IF statements.

An easier approach is to use the OR function within the IF statement. The syntax of the OR function is =OR(logical1, [logical2], [logical3]…). You can have a multitude of logical tests, and as long as one of them is true, you will get a positive result. If none are true, then a negative answer will result.

Our formula for the IF & OR combination is:

=IF(OR(B2>E2,B6>E6,B9>E9),”YES”,”NO”)

ifor3

When you compare the two formulas, you can see that the IF & OR combination is much easier to construct and more logical to review and evaluate:

ifor4

And there you have it. Using IF & OR makes testing for multiple logicals a much simpler approach!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , ,

No comments yet.

Leave a Reply