Explanation

The goal is to demonstrate how other formulas and functions can be nested inside the IF function . The example is a simple quantity-based discount formula.

IF function

The IF function evaluates a logical test and returns one value if the result is TRUE, and a different value if the result is FALSE. The generic syntax for IF looks like this:

=IF(logical_test,value_if_true,value_if_false)

For example, if cell A1 contains the value 75, then you could use IF to return “Pass” or “Fail” like this:

=IF(A1>70,"Pass","Fail") // returns "Pass"

If the value in A1 is 65, then the same formula will return “Fail”:

=IF(A1>70,"Pass","Fail") // returns "Fail"

What is not obvious with IF is that the logical_test , the value_if_true, and the value_if_false can all be other formulas . The example below shows how this works.

Example

In the worksheet shown, the goal is to apply a simple quantity-based discount to the total calculated in column E. If the quantity is greater than 20, we want to discount the total by 10%. Otherwise, we want to calculate the total normally. In cell E5, the formula used to perform this task is:

=IF(C5>20,C5*D5*0.9,C5*D5)

The formula works like this:

  • The logical test is C5>20. This checks if the quantity of items sold (in cell C5) is more than 20.
  • The value if true is C5D50.9. This calculates the total price as the quantity sold times the price per item and then applies a 10% discount by multiplying the result by 0.9.
  • The value if false is C5*D5. This calculates the total price as the quantity sold times the price per item, without any discount.

So, if more than 20 items were sold, the formula applies a 10% discount to the total price. Otherwise, it just calculates the total price without any discount. You can then copy the formula down column E to apply it to all items in the spreadsheet.

Other calculations

The calculations used inside the IF function can be customized as needed. For a more general formula that applies the discount itself, you can use the following:

=IF(C5>20,C5*D5*(1-discount),C5*D5)

For example, to apply an 18% discount, you would use:

=IF(C5>20,C5*D5*(1-18%),C5*D5)

This works because Excel will automatically evaluate the percentage 18% as the number 0.18. You can also adjust calculations in the logical test as needed. To apply a 20% discount to apples only, you could use the AND function in the logical test like this:

=IF(AND(C5>20,B5="apples"),C5*D5*(1-20%),C5*D5)

In Excel, nesting other calculations inside a function or formula is a common practice in many more advanced formulas. You can find many examples in this list .

Detailed quantity-based calculation

If you are interested in a more detailed example of a quantity-based discount formula, this example uses XLOOKUP instead of IF to apply more granular discounts based on a lookup operation.

Explanation

The goal of this formula is to verify whether the values in column B follow the format xx-xxxx-xxx, where “x” represents any single character. The IF function doesn’t support wildcards directly, so we can’t use IF by itself. Instead, we can combine the IF function with the COUNTIF function, which does support wildcards.

Excel wildcards

Excel supports three wildcards that can be used in formulas:

  • Asterisk (*) - zero or more characters
  • Question mark (?) - any one character
  • Tilde (~) - escape for literal character (~*) a literal question mark (~?), or a literal tilde (~~).

IF + COUNTIF

Unlike several other frequently used functions, the IF function does not support wildcards. However, you can use the COUNTIF or COUNTIFS functions inside the logical test of IF for basic wildcard functionality . In the example shown, the formula in D5 is:

=IF(COUNTIF(B5,"??-????-???"),"ok","invalid")

Working from the inside out, the logical test inside the IF function is based on the COUNTIF function :

COUNTIF(B5,"??-????-???") // returns 1 or 0

In this context, COUNTIF counts cells matching the pattern “??-????-???”, with the question mark (?) representing any single character. Because the range provided to COUNTIF is just one cell, the result will always be 1 or zero. Inside the IF function, Excel will evaluate any non-zero number as TRUE and zero as FALSE. When COUNTIF returns the number 1 (indicating that the value in B5 consists of 11 characters with two hyphens as described by the pattern), IIF interprets this as TRUE and returns “ok”. When COUNTIF returns zero, IF will return “invalid”. The values returned by IF can be customized as needed.

IF + SEARCH

Another way to use wildcards with the IF function is to combine the SEARCH and ISNUMBER functions to create a logical test. This works because the SEARCH function supports wildcards:

=IF(ISNUMBER(SEARCH("??-????-???",B5)),"ok","invalid")

The SEARCH function returns the position of a match inside a text string. If SEARCH does not find a match, it returns a #VALUE! error. The ISNUMBER function is used to convert the result from SEARCH into TRUE or FALSE. Like COUNTIF, SEARCH supports wildcards, so we can use the same pattern to check for invalid codes. For more details on how SEARCH and ISNUMBER can be used together, see this page.

Note: The COUNTIF function has a limitation – the range argument must be a range . It’s not possible to pass an array from another function into COUNTIF. If you run into this problem, you can use SEARCH + ISNUMBER option above instead.