Excel IF function: Explained with 5 Formulas

The IF statement is generally taken as a decision-making statement – to take a decision based on certain options/conditions. If the given condition is TRUE then execute this and for FALSE do that.

The if statement also enables evaluating multiple options, as such in the real world, decisions may be based on more than two options.

In Excel, the IF is a function that enables us to do that. For example, you have a mark sheet of students that contains Student ID, Name, Marks, and Result. The Result column is based on Marks with two options: Pass/Fail.

In terms of the IF function:

If marks are greater than or equal to 60 then the Result is Pass.
Else (for less than 60) the result is Fail.

This is how you may write the Excel IF Formula for this scenario:

=IF(C2>=60,"Pass","Fail")

On that basis let us look at the general syntax and parameters of IF Function:

IF(logical_test, True_Value, [False_Value])

The logical_test specifies the condition that we want to check. In our example formula, the C2>=60 is the logical_test. You may use ‘=’, ‘<=’, ‘<’, ‘>’ and other operators there.
If the logical_test is evaluated as True, the IF function returns True_Value. In the example formula, “Pass” is the True_Value. This is also required.
If logical_test is False, the False_Value is returned by Excel IF function. In the example, the “Fail” is the False_Value. This is optional.
The simple IF function has two results (True/False). If you require more results then use Nested IF. The Nested If functions can have up to 64 results.

Let me first start with the example of using a simple IF Excel function. After that, I will also show you using the Nested IF function for more than two results and using IF with AND/OR function.

A simple example of IF in Excel

In the example of a simple IF function, I will use the same scenario as in the introductory section. If Marks is greater than or equal to 60 then the Result column should be updated by “Pass”. For less than 60, it should be Fail. Have a look at the formula and demo excel sheet.

=IF(B5>=60,"Pass","Fail")

Testing two columns example in IF “Else” function

For this example, two column values are compared in the IF function. The A column contains the Earnings and the B column contains Expenses.

If Expenses are greater than Earnings then the C column (Status) should display “Over Budget” else it should display “Controlled”.

The formula for C2 cell is:

=IF(A2>B2,"Controlled","Over Budget")

You saw, we used the greater than operator in the above formula i.e. “>”. Alternatively, you may also use “<=” for the same result as follows:

=IF(B2<=A2,"Controlled","Over Budget")

The example of using calculated result for True/False values

In the above examples, we used fixed text for True or False values. For example, Pass, “Controlled”, “Over Budget” etc. In the following example, by using the same excel sheet, we will calculate if the budget is left or it is already over.

For that, we will use the “A-B” columns in the IF Function as follows:

The IF Function formula:

=IF(B2<=A2,A2-B2,0)

So, the IF function tested B2 cell value (Expenses) is less than or equal to A2 (Budget). If this is True i.e. amount still left for expenses, the remaining amount is displayed in the adjacent column i.e. D2. If False then it will simply display 0. The same formula is applied to the other cells (D2 to D4):

The example of IF and ‘AND’ Excel functions

In all above examples, we tested a single condition in the IF function. In certain scenarios, you may require testing multiple conditions in a single IF function. For that, you may use the AND with IF function in excel.

As using the AND function, if all the conditions evaluate as True, the True value will be returned. If any of the conditions is False, the False value is returned.

To demonstrate that, the AND function is used with IF function in the same excel sheet as in the previous example, and the following formulas are applied to the D2, D3, D4, and D5 cells:

=IF(AND(A2>2000,B2<=2000),"All True","All or at least one is False")

If you look carefully at the graphic above, the A2 = $2500 (True) and B2 = 2000 (True). The end result is thus True.

The D3 formula:

=IF(AND(A3>4000,B3<=3000),"All True","All or at least one is False")

In the excel sheet, the A3 = 3500 (False) AND B3 = 3700 (True). The end result is “All or at least one is False”. Because one condition is false.

The D4 formula:

=IF(AND(A4>8000,B4>6000),"All True","All or at least one is False")

Actual values in Excel sheet for A4= $8000 (False, as we checked greater than) and B4 = 6500 (True). As one is True and other is false, so the IF Function result is “All or at least one is False”.

The D5 IF AND Excel Formula:

=IF(AND(A5=5500,B5<=5700,C5="Over Budget"),"All True","All or at least one is False")

There we used three conditions. Let us check if any of the three is False. The excel sheet contains the following values:

A5 = 5000 (True)
B5 = 5700 (True)
C5 = “Over Budget” (True)

So all the conditions are true and the returned value is All True.

The Example of IF / OR functions for multiple conditions

You may also use OR function with IF for multiple condition testing. The difference between the Excel OR and “AND” functions is, the IF returns True_Value if any of the conditions are True. While, in the case of AND we saw, all the conditions have to be True.

Using the same sheet as in AND function, let us have a look at the outcome as using the IF OR Excel functions together.

Following IF formulas are used with OR for D2 to D5 cells:

D2 Formula with IF/OR:

=IF(OR(A2>2000,B2<=2000),"All or at least one is True","All False")

D3 Formula:

=IF(OR(A3>4000,B3<=3000),"All or at least one is True","All False")

D4 Formula:

=IF(OR(A4>8000,B4>6000),"All or at least one is True","All False")

D5 Formula:

=IF(OR(A5=6000,B5<=5700,C5="Controlled"),"All or at least one is True","All False")

If you compare the values used in formulas with the excel cell values (graphic) below you will understand how OR function works with IF for evaluating multiple conditions:

Only for D3 cell formulas, all conditions are False. For other cells, at least one condition is True so it returned True_Value.

The Nested IF Example

The Nested IF means using multiple IF functions in a single formula. The simple IF function results in two possibilities. That is, a True value or False. There are situations where you may require dealing with more possibilities. For example, take the Student marks scenario (as in the first demo). There, we only had two outcomes: Pass or Fail.

Let me extend that example to demonstrate how Excel Nested IF (IF..elseif) works. Take this situation:

If marks are between 1 to 40 then the result should be E
If marks are between 41 to 50 then the result should be D
51 to 60 = C
61 to 70 = B
71 to 80 = A
81 to 100 = A+

For that, column B2 to B7 is filled with marks. Column C2 to C7 display the result from E to A+ by using the Nested IF formula.

The following IF formula is used in the C2 cell:

=IF(B2<=40,"E",IF(B2<=50,"D",IF(B2<=60,"C",IF(B2<=70,"B",IF(B2<=80,"A",IF(B2<=100,"A+"))))))

C3 Formula:

=IF(B3<=40,"E",IF(B3<=50,"D",IF(B3<=60,"C",IF(B3<=70,"B",IF(B3<=80,"A",IF(B3<=100,"A+"))))))

And so on. Just replace the B2 to B3, B4, and so on.

The resultant sheet is shown below:

You see, there is a lot of opening and closing parenthesis in the nested IF formula. When using this, you should be careful as this may end up in error or unexpected results.

The benefit of this, as in the above example, we may check multiple options in a single function with a range of values.

Note: In order to run this formula in your system, you must enter a number between 1 to 100. Or adjust the number range accordingly in the IF formula.