top of page

IF + OR


I’ve covered “IF statement” previously. A limitation of “IF statement” is that it allows for only ONE criteria - “if x is equal to / less than / greater than y then do this else do that”.

What if you want more than one criteria? There are many options that allow for this, but one of the most simplest way is using a combination of two formulas / functions: IF and OR.

Before I get into the crux of those formulas, let’s define OR. OR is “used as a function word to indicate an alternative, e.g. coffee or tea” - Merriam-Webster Dictionary. Another word for OR is 'either'.

Excel Formula: =IF(OR(logical1, [logical2]*,...), value_if_true, value_if_false)

*Remember, elements of the formula that is in square brackets are optional.

Formula Made Easy

If you remember, the first element in an IF statement is logical_test. Notice that's missing in the formula. Instead, the OR statement replaces the logical_test element of the IF statement.

Example 1

In my Mad Hatter Chocolatier data, I have a list (in the RAW DATA tab) of orders. In columns A to C, I have the client’s title, first name & last name. I want to assign each client their gender based on their title.

A client can have one of the titles below:

  • Sir (Male)

  • Mr (Male)

  • Miss (Female)

  • Mrs (Female)

If I were to assign the first client a gender, my Excel formula will look like:

ALERT! Make sure to put a close bracket after the OR statement. IF and OR formulas are two separate statements combined into one formula.

Formula Applied

If I were to fill in the cell references into my Formula Made Easy table, it will look like:

In plain English, the above formula means “if cell A2 says “Sir” OR “Mr”, then the gender is “Male”, otherwise it’s “Female”.

I can easily drag the formula down (autofill) the column by double-clicking on the black cross when I put my mouse at the bottom right hand corner of the cell.

Example 2

I have a list of student results from two tests: Test A and Test B. In order for the student to pass a grade, they need to score at least 50 marks in ONE OF the tests.

Based on the above scenario, what will my formula look like? If I were to do the first entry:

In plain English, the above formula means “if the mark in cell A2 (Test A) is greater than or equal to 50, OR the mark in cell B2 (Test B) is greater than or equal to 50, then the student has a“Pass” result, otherwise a “Fail” result.

If I autofill the formula to the remaining rows, the overall result looks like:

Reference:

Opmerkingen

bottom of page