IF() Statement Function in Excel - How to use it

The IF statement is a simple yet powerful tool. Today we will go through how the IF function works and some useful examples of where it can be used.

The IF function specifies a test to perform. It then returns a response based on the outcome of that test. There is only 2 possible outcomes of the test. It can either validate true or false. To better understand this concept, you should first look at the equation in its three individual parts. The equation is as follows:

=IF(logic test, value if true, value if false)

The logic test is the first argument. This is the rule that validates either true or false. So for example, you could test if your employees have went over budget:

Below is a list of employees and their expenses for the month:

You can enter the IF formula in cell C2 to evaluate the value of the expenses in cell B2. Say you have a limit on expenses of 150, you would have the following to start:

=IF(B2>150,

This tests if the value in cell B2 is greater than 150. In this example, the value of cell B2 is 128. As 128 is lower than 150 the test evaluates false.

This takes us on to the value if true and value if false parts of the IF function. The second argument is the value if true. This is the value displayed in the cell (C2) if the test evaluates true. In the above example, Greater than would be displayed in C2 if the test was evaluated to be true:

=IF(B2>150,Greater than,

The third argument is the value if false. This is the value displayed in the cell if the test evaluates false. Looking at the above example, Less than would be displayed in cell C2 if the test evaluated to be false.

Considering these all together we get the full formula:

=IF(B2>150,Greater than,Less than)

This is the formula that is entered into cell C2 in the spreadsheet below:

The value in B2 is 128. As this is less than 150, Less than is displayed. This is because Less than is the third argument in the IF function and is the value displayed if the outcome of the test is false.

In this example the greater than symbol was used as the logic test. Alternatively you could use a less than symbol to test whether the value is lower than 150. Any number of mathematical operators can be used. (+, -, =, /,<). You can even combine mathematical operators and use multiple cells in your test logic (also works with the value if true and value if false). Using the previous example I have added a discount column:

The discounts can be subtracted from the expenses in the same IF function to see if an employee is over budget with the discounts apploed to their expenses. In cell D5 I have entered the following:

=IF(C5-B5>150,Greater than, Less than)

This is similar to before but the first argument has been changed to incorporate the discounts. The logic test evaluates whether the expense in cell C5 (200) minus the discount in cell B5 (51) is greater than 150. As 200 51 is 149 and less than 150, the test evaluates true. This then displays the value if true portion of the IF formula in cell D5.

Have a go using the IF function yourself to see how it works. Once you get to grips with it you can combine it with other functions (e.g. AND, OR) and you can even place IF functions within IF functions. These are actually called nested IFs and add further layers of complexity but shouldnt be too difficult to understand once you have your head around the IF function itself. You can refer to the previous tutorial on using multiple functions within a single cell to really advance your understanding and expand your usage of the IF function.