How To Use the IF Function in Excel

In a Nutshell

The IF function is an extremely versatile logical function in Excel. It is used to perform a logical test to see if a certain condition is true or false. For example, “Is the value in cell C5 greater than 50?”. If the logical test is TRUE it returns a result (e.g. “Pass”), and if the logical test is FALSE it returns a different result (e.g. “Fail”).

The IF function, like many other functions in Excel, is most powerful when used in combination with other Excel functions (see examples below).

Syntax

=IF(logical_test, [value_if_true], [value_if_false]) (Square brackets indicate optional arguments)

As we can see from the structure of the function, it has three arguments:

1.logical_test

This is the condition that we give to the function to test for. E.g. “is cell A1 equal to 500”?

2.value_if_true

This is the value that is returned if the logical_test condition is TRUE.

(Note: This argument is optional. If we do not supply a value_if_true condition, the function output is “TRUE” if the condition is True.)

3.value_if_false

It is the value that is returned if the logical_test condition is FALSE.

(Note: This argument is optional. If we do not supply a value_if_false condition, the function output will automatically generate “FALSE” if the condition is False.)

Logical Operators

When constructing the logical test (i.e. the first argument in the IF function), we can use any of the logical operators given in the table below;

Examples

Let’s take a look at a few examples of how we can use the IF function in Excel, and how this function can be combined with some of Excel’s other basic and more advanced functions.

1. Use of IF function in Excel

In the first example, we have children going on a school trip to an area where only students of age 14 or above are allowed to go, and we want to create a formula that quickly identifies who is included in this group.

We have a spreadsheet showing the names of the students and their ages (see Figure 1):

Figure 1

Now we can create another column in the sheet, so that students whose age is below 14 can simply be filtered out later. For this task we use the IF function (see Figure 2):

Figure 2

Looking at cell D3 we can see the formula is as follows:

=IF( C3 >= 14, “Allowed”, “Not Allowed”)

What this means is:

If the value in cell C3 is greater than or equal to 14, then display Allowed, otherwise, display Not Allowed.

So in the first formula, in cell D3: Because Alex is 15, the logical test evaluates to true (as 15 is greater than or equal to 14), so the value_if_true is displayed in the cell (i.e. Allowed).

For Anderson, in cell D4, the logical test evaluates to false, because 12 is less than 14, so the value_if_false is displayed in the cell instead (i.e. Not
Allowed).

2. Use of IF function with DATEDIF & TODAY() functions in Excel

The IF function, like most other functions in Excel, can be combined with other functions to make our life easier.

In this example, we have the same situation as in Example 1, but this time in column B instead of students’ ages, we are presented with each students date of birth (see Figure 3).

Figure 3

Now, we want to see if a student is allowed to go for a trip (students with age 14 or above) or not. Here we use DATEDIF and TODAY () function combined with IF function to show the status of a student for the trip (See Figure 4).

Figure 4

Explanation:

The DATEDIF function finds the difference between two dates in Years, Months and Days, while the TODAY () function gives us today’s date. So, in the logical test argument of the IF function we first use these two functions to find the difference in years, and then we check whether the difference is greater than or equal to 14. The value_if_true and value_if_false arguments are the same as in Example 1 above.

3. Use of IF function with AVERAGE function

In this example, we have the sales data of different products’ units in four different months of the year (See Figure 5).

Figure 5

Let’s assume that after taking into consideration all the costs, we figured out that if the average sales of each product is greater than or equal to 25 units, then that product is in Profit, otherwise it is in Loss.

Here, IF function is combined with AVERAGE function to show us the status of a particular product (See Figure 6).

Figure 6

Explanation:

The AVERAGE function in Excel finds the average of a range of values. For the IF function the logical_test is to see if the returned value from the AVERAGE function is greater than or equal to 25. The 2^nd argument of the IF function (value_if_true) is “Profit”, while the 3^rd argument (value_if_false) is “Loss”.

4. Use of IF function with AND function

In this example, we have supposed a sheet of data reflecting the mileage and acceleration of different types of cars (See Figure 7).

Figure 7

Now, we want to keep only those cars whose mileage is above 25 MPG and acceleration is below 8 sec to 60mph. For this purpose, IF function is combined with an AND function to show if we will accept a car or reject it (See Figure 8).

Figure 8

In figure 8, we can see that Cars Type-5 and Type-8 meet our requirements, and hence are accepted.

Explanation:

An AND function returns “TRUE” if all the criteria are satisfied, or “FALSE” if any of the criteria is not satisfied. Here, IF function is combined with AND function, and returns “Accept” if the AND function is evaluated as TRUE, otherwise it returns “Reject” into the cell.

5. Use of IF function with OR function

In this example, we have supposed the same data as in Example 5 (See Figure 9).

Figure 9

Now, we want to keep only those cars whose either mileage is greater than 35 (MPG) or acceleration is less than 7 (Sec to 60mph). For this purpose, IF function is combined with an OR function to show if we will accept a car or reject it (See Figure 10). In other words, if even just one of the criteria is true, then the IF function evaluates to TRUE.

Figure 10

In Figure 10, we can see that Cars Type-4, Type-5, Type-6, Type-8, Type-9 meet at least one of the 2 criteria we have discussed above.

Explanation:

An OR function returns “TRUE” if any of the multiple criteria is satisfied and “FALSE” if none of the multi criterion is satisfied. Here, IF function is combined with OR function, and if the OR function is evaluated as TRUE it returns “Accept” otherwise it returns “Reject” into the cell.

Summary

The IF function is used to perform a logical test for a certain condition (e.g. “Is the value in cell C5 greater than 50?”), and then returns one value if the logical test is TRUE (e.g. “Pass”), and returns a different result if the logical test is FALSE (e.g. “Fail”).

The IF Function has three arguments, one is the condition it runs the logical test upon, the 2^nd argument is the value it returns to the cell if the condition is TRUE, while the 3^rd argument is the value it returns when the result of the logical test is FALSE.

Note: The value_if_true and value_if_false arguments are optional. If these arguments are not defined, it will return “TRUE” or “FALSE” based on the result of logical test. For example, IF(1=2) will return an output of FALSE, because we have a logical test (1=2), but we didn’t specify what needs to happen when the logical test is false. So it delivers an output of FALSE.