However, beware that the IF function is deceptively simple.
Formulas that use IF can quickly become advanced as the requirements become more complex.
Contents
The IF function is one of the most widely used functions in Excel.
However, beware that the IF function is deceptively simple.
Formulas that use IF can quickly become advanced as the requirements become more complex.
Sometimes, the IF function doesn’t even make sense for the problem.
To avoid this fate, it pays to understand the basics well.
you’re able to see the result in the worksheet below.
Also, we do not need to provide a value for value_if_false because IF will return FALSE by default.
This formula works fine.
But the output is rather busy and hard to read.
Text values hardcoded into formulas must always be quoted.
Finally, let’s modify the formula to display nothing when a score is not over 80.
We now see an “x” for scores over 80.
Numbers like 80 are not quoted.
Usually, thelogical_testin IF is an expression that will return TRUE or FALSE.
However, numbers and logicaloperatorslike >,<,<>,=, etc., arenot quoted.
A passing score is 70 or higher.
Also, note that the logical flow of this formula can be reversed.
Above, we test for failing scores.
Otherwise, return “Fail”.Both formulas will return the same result.
Which option you choose is a personal preference.
The trick is to combine the IF function with the OR function.
Otherwise, return an empty string ("").
Notice that the OR function is embedded inside the IF function as the logical test.
The OR function accepts multiple logical expressions.
Ifanyexpression returns TRUE, the OR function will return TRUE.
Only if all logical expressions return FALSE will the OR function return FALSE.
The “B” appears in quotes ("") because it is a text string.
The 80 is not quoted because it is a number.
Each IF statement needs to be carefully “nested” inside another so that the logic is correct.
The idea here is that we are chaining together multiple IF functions to create “else if” logic.
In the example below, the goal is to assign a number to each color.
If the color is Red, the result should be 100.
If the color is Blue, the result should be 125.
If the color is Green, the result should be 150.
Note that this formula will only handle three colors: Red, Blue, and Green".
With any other color, the formula will return FALSE.
In addition, the IF function can also return a formula.
The price of other colors should remain unchanged.
If the color is Red, it returns D5*1.2, effectively increasing the price by 20%.
If the color isnotRed, it simply returns D5.
IF with wildcards and regex
The IF function does not supportwildcardsdirectly.
However, you’re free to combine IF with other Excel functions to overcome this limitation.
For basic wildcard support, you cancombine IF with COUNTIForcombine IF with SEARCH(as seen above).
This can be done with the pattern “"\b(MN|MT|ND|SD)\b”.
However, just because IF is common doesn’t mean you should always use it.
Excel hasmanyfunctions that don’t need IF to apply “If else” logic.
Likewise,COUNTIFSandSUMIFScan count/sum matching values based on multiple criteria without IF.
See101 Functions You Should Know,Dynamic Array Formulas in Excel,andNew Excel Functionsfor more information.
AND returns TRUEonly if all the conditions are met.
If any conditions are not met, the AND function returns FALSE.
OR returns TRUEif any condition is TRUE.
If all conditions are FALSE, the OR function returns FALSE.
When given TRUE, NOT returns FALSE.
When given FALSE, NOT returns TRUE.
Use the NOT function to reverse a logical value.
Use the IFS function to evaluate multiple conditions without multiple nested IF statements.
When no match is found, SWITCH can return an optional default value.
