Building standalone logical IF functions, and making them more complex by nesting AND and OR within them.
Where/when to use the technique
Constructing models that allow you to dynamically react to changes in inputs, returning different results based on the inputs.
The IF Function
- Basic 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”
Lesson notes are only available for subscribers.