Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
Learn to build standalone logical IF functions, and make them more complex by nesting AND and OR within them.
Multiple versions of this lesson are available, choose the appropriate version for you:
Building standalone logical IF functions and making them more complex by nesting AND and OR within them.
When to use
Constructing models that allow you to dynamically react to changes in inputs, returning different results based on the inputs.
- Create a new worksheet
The IF Function
- Syntax: =IF([Scenario to test],[What to do if TRUE],[What to do if FALSE])
- =IF(A1>10000,”We’re rich!”,”We need money”) returns “We’re rich” if A1 is greater than 10000, and “We need money” if A1 is less than or equal to 10000.
Key points to remember: The “Scenario to test” must always be something that evaluates to TRUE or FALSE.
AND vs OR
- =AND(A5=5, B5=6) will return TRUE if A5=5 AND B5=6. (Both conditions must be met.)
- =OR(A5=5, B5=6) will return TRUE if either A5=5 OR B5=6. (Either condition can be met.)
Key points to remember: An AND or an OR function can be nested inside an IF function’s “Scenario to test”, allowing it to dynamically react to changes in our data setup.
- =IF(OR(A1>10000,B1<100000),”We’re rich!”,”We need money”)
- Will return “We’re rich” if A1 is greater than 10,000, OR if B1 is less than 100000
- If neither of the above are true, it will return “We need money”
Hints & tips
In addition to the ones covered here, there are many other logic functions available in Excel.
Lesson notes are only available for subscribers.