How to Use If and Nested If Statements in Excel

One Excel function that I use quite often in my formulas is the IF function. The IF function is used to test a boolean condition and get two different results depending on whether the boolean condition returns TRUE or FALSE.

Take the mobile phone sales table below as an example. You can download the sample file here.

IF Position With One Condition

Consider a scenario where you need to calculate the commission for each sales line based on where the sales were made (column D). If the sales were made in the United States, the commission is 10%, otherwise the rest of the regions will be charged a commission of 5%.

The first formula you need to enter in cell F2 looks like this:

= IF (D2 = “USA”, E2 * 10%, E2 * 5%)

Formula breakdown:

1. = IF (- “=” indicates the beginning of the formula in the cell, and IF is the Excel function we are using.
2. D2 = US is the boolean test that we perform (i.e., if the data in column D2 is US).
3. E2 * 10% is the result that will be returned by the formula if the original boolean test is TRUE (i.e. the value in column D2 – US).
4. E2 * 5% – The result that will be returned by the formula if the original boolean test returns FALSE (i.e. the value in column D2 is NOT US).

Then you can copy the formula from cell F2 to the rest of the rows in column F, and it will calculate the commission for each row by 10% or 5% depending on whether the boolean test IF returns TRUE or FALSE for each row. line.

– /

IF Job with Multiple Conditions

What if the rules were a little more complex, where you needed to test more than one boolean condition with different results returned for each condition?

Excel has the answer! We can combine multiple IF functions in a single cell, which is sometimes called nested IF.

Consider a similar scenario where commissions are different for each outlet as shown below:

• USA 10%
• Australia 5%
• Singapore 2%

In cell F2 (which will later be copied to the rest of the rows in the same column F), enter the following formula:

= IF (D2 = “USA”, E2 * 10%, IF (D2 = “Australia”, E2 * 5% , E2 * 2%))

Formula breakdown:

1. = IF (is the beginning of the formula using the IF statement
2. D2 = ”USA” is the first logical test we run (ie, if the data in column D2 – US).
3. E2 * 10% is the result that will be returned by the formula if the original boolean result is TRUE (i.e., the value in column D2 is US).
4. IF (D2 = Australia, E2 * 5%, E2 * 2%) is the second Excel IF statement that will be evaluated if the original boolean check resulted in FALSE (that is, the value in column D2 is NOT US). The one-condition IF function syntax discussed earlier in this article, where if the value in cell D2 is Australia, it returns E2 * 5%, otherwise if the value is not Australia, the function returns E2 * 2%.

Because Excel will evaluate the formula from left to right, when you run a boolean test (for example, D2 = “USA”, the function will stop and return the result, ignoring any subsequent boolean test (for example, D2 = “Australia”).)

Thus, if the first boolean test returns FALSE (i.e., a non-US location), it will continue to evaluate the second boolean test. If the second boolean test also returns FALSE (i.e., the location is not in Australia), then we do not need to do further testing, since we know that the only possible value in cell D2 is Singapore, so it should return an E2 * 2% result.

If you prefer for clarity, you can add a third boolean test IF (D2 = Singapore, value if TRUE, value if FALSE). Therefore, the full extended formula looks like this:

= IF (D2 = “USA”, E2 * 10%, IF (D2 = “Australia”, E2 * 5%, IF (D2 = “Singapore”, E2 * 2%)))

As mentioned earlier, the above will return the same result as the original formula we had.

= IF (D2 = “USA”, E2 * 10%, IF (D2 = “Australia”, E2 * 5%, E2 * 2%))

• For each individual IF function (there must be an opening and closing parenthesis. When there are three IF functions, as in one of the above examples, the formula will need three closing parentheses “)))”, each of which marks the end of the corresponding opening IF (statement.
• If we do not specify the second result of the boolean check (when the boolean check resulted in FALSE), the default value assigned by Excel will be the text “FALSE”. So the formula = IF (D2 = “US”, E2 * 10%) will return the text “FALSE” if D2 is not “US”.
• If you have several different boolean tests each with its own result, you can combine / nest the IF function several times, one after the other, as in the example above.

–

Quick Tips

• For every IF ( function, there must be a circular arc to open and close. When there are three IF according to For one of the above examples, the formula will need three closing parentheses “)))” , each denoting the end of the corresponding IF ( opening clause).
• If not We define the second result of the logic test (when the logic test leads to FALSE “FALSE”. So the formula = IF (D2) = “USA”, E2 * 10%) will display the text “FALSE” if D2 is not of the type the crimes of the same name, which are generated By {1} {/ 1}.
• If you have multiple different Boolean tests each with different results, you can merge / include the IF element that works multiple times, one by one, on Similar to the example above.