Excel IF function

How to use the Excel IF function

Description

The Excel IF function is one of the most used and powerful functions in Excel. It allows you to perform a simple logical test and returns one value for a TRUE result, and another value for a FALSE result. 

Syntax

IF( logical_test, [value_if_true], [value_if_false])

Arguments

logical_test – The condition that you want to test

value_if_true – [Optional] The result you want to be returned if the logical_test result is TRUE

value_if_false – [Optional] The result you want to be returned if the logical_test result is FALSE

Return value

The corresponding value that you define for TRUE or FALSE.

How to use the IF function in Excel

Use the IF function to perform a logical test with specific return values for a TRUE and FALSE result.

In the above example, we want to assign either a “Pass” or “Fail” based on a given exam score. In order to pass the test 50 or more points are required. The formula in D8 is:

= IF(C8>=50, “Pass”, “Fail”)

The IF function can be used to evaluate both values and text. For logical tests on text you are limited to checking if one thing is equal to another, so you can only use the “=” operator in the logical test:

= IF(E1=“Done”, 1, 0)

However, for logical tests on numerical values you can use any logical operator and perform additional calculations.

Logical Operators

Here is an overview of all logical operators you can use to construct a logical test with IF:

Logical Operator
Meaning
Example
=
equal to
A1 = C1
<>
not equal to
A1 <> C1
>
greater than
A1 > C1
<
smaller than
A1 < C1
>=
greater than or equal to
A1 >= C1
<=
smaller than or equal to
A1 <= C1

Combination of IF with AND & OR

if you want to test multiple conditions in your IF statement at once, you can use the AND function or OR function to check if either all conditions or at least one of multiple conditions are TRUE.

Let’s say you want to return “Good” if the value in A1 is between 15 and 20 and “Bad” if the value is outside this range, then you can use the following formula with AND inside the IF function:

= IF(AND(A1>15,A1<20), “Good”, “Bad”)

To return “Red” if A1 is either “Hearts” or “Diamonds”, you can use OR inside the IF function:

= IF(OR(A1=“Hearts”, A1=“Diamonds”), “Red”, “Black”)

Nested IF functions

To test multiple conditions and return different values depending on the results, you can nest multiple IF functions inside each other. “Nested” in this context means that you insert another IF statement for the value_if_true or value_if_false argument of another IF statement.

The most common structure of a nested IF statement looks as follows:

IF( logical_test_1, result_1, IF( logical_test_2, result_2, IF( logical_test_3, result_3, result_4)))

As an example, let’s assign different grades to the exam scores in our above example instead of only “Pass” or “Fail”:

= IF(C8>65, “A”, IF(C8>60, “B”, IF(C8>55, “C”, IF(C8>=50, “D”, “F”))))

Even though nesting the IF function is possible for up to 64 IF functions, we recommend to use an alternative function for more complex scenarios. Here is a list of alternative functions for checking multiple conditions:

Special IF functions

In Excel there are many functions that can be considered to be special cases of the IF function. On the one hand, there are functions that save you time as the logical test is already predefined:

  • IFERROR function to handle errors in a cell and return a supplied value instead of the error
  • IFNA function to handle NA errors in a cell and return a supplied value instead of the error

On the other hand, there are many functions that allow you to apply simple calculation tasks on arrays of values depending on a logical test applied for every single cell in the array:

AND formula examples

Related Tutorials

Related Functions

How to use the Excel OR function

Excel OR function

The Excel OR function is a logical function that is used to check if at least one of multiple conditions is evaluate TRUE.

How to use the Excel IFS function

Excel IFS function

The Excel IFS function allows you to perform multiple logical tests and returns a value that corresponds to the first TRUE result

How to use the Excel AND function

Excel AND function

The Excel AND function is a logical function that is used to check multiple conditions at the same time. AND returns TRUE if all conditions evaluate TRUE.

Facebook
Twitter
LinkedIn
Reddit
WhatsApp
Email