Table of Contents

### About

The Excel **IF **function is used to perform a logical test and return a value based on the test result. One value for **TRUE **result and another value for **FALSE **result.

**Function Type**

Logical

**Purpose**

Test a specific condition

**Return value**

One of the two values supplied for TRUE or FALSE

**Syntax**

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

**Arguments**

**logical_test** – A logical expression that can be evaluated as TRUE or FALSE

**value_if_true** – [optional] The value to return when the logical_test evaluates to TRUE

**value_if_false **– [optional] The value to return when the logical_test evaluates to FALSE

**Examples**

**Check the score of a candidate and return PASS or FAIL on the basis of the score value**

**Check the score of a candidate and return PASS or FAIL on the basis of the score value**

In this example, IF function is used to check whether the score of the candidate is **Greater than or Less than 60** and based on the test result, return the text **“PASS”** or** “FAIL**“.

=IF(C3>60,"PASS","FAIL")

The literal translation of the above formula is ‘*If the value in the cell C3 is greater 60, return the text PASS, otherwise return the text FAIL’. *

The logical flow of this test can be reversed and the same result can be obtained. i.e. instead of checking whether the score is greater than 60 and return PASS or FAIL, the formula will check whether the score is less than 60 and return FAIL or PASS.

=IF(C3<60,"FAIL","PASS")

=IF(C3>60,"PASS","FAIL")is same as=IF(C3<60,"FAIL","PASS")

In the above example, we have hardcoded the pass mark, i.e. the value **60** into the formula. Now, we will use a cell reference **C2** that contains the pass mark, inside the IF function.

=IF(C5>$C$2,"PASS","FAIL")

here the formula checks whether the value in the cell C5 is greater than the value in the cell C2 and return PASS or FAIL based on the result.

*Arithmetic operations using IF Function*

In the following example, IF function is used to find out the non-negative difference between values in columns A and B. The formulas A-B or B-A may sometimes return a negative value. But the following formula using IF function will always return a positive value.

=IF(B3>C3,B3-C3,C3-B3)

The literal translation of the above formula is ‘*If the value in the cell B3 is greater than the value in the cell C3, subtract the value in the cell C3 from that in the cell B3 and return that value, otherwise subtract the value in the cell B3 from that in the cell C3 and return that value.*‘

One more example where IF function is used to compare two values and perform multiplication or division between them.

=IF(B3>C3,B3/C3,C3*B3)

The function will check whether the value in the cell B3 is greater than the value in the cell C3. If the value in B3 is greater than the value in the cell C3, the value in B3 is divided by the value in C3 and the function will return that result. If the value in the B3 is not greater than the value in the cell C3, the function will return the product of values in B3 and C3.

### NESTED IFs

When an IF function is used within another IF function, that technique is called Nesting of IFs. This is usually done to check multiple conditions.

Following is an example in which the IF function is used to check a score and return different grades based on the score value.

=IF(C3<70,"D",IF(C3<=80,"C",IF(C3<=90,"B","A")))

The above formula when broken down into steps, will read like the following

*Step 1:* Check whether the value in the cell C3 is less than **70**. If so, return the text **D** otherwise, go to Step 2 (Second IF function)

*Step 2:* Check whether the value in the cell C3 is less than **80**. If so return the text **C **otherwise, go to Step 3 (Third IF Function)

*Step 3:* Check whether the value in the cell C3 is less than **90**. If so return the text **B** otherwise return **A**.

## Logical Operators for IF Function

The following are the list of operators that can be used inside IF function to perform logical tests.

Comparison Operator | Meaning | Example |
---|---|---|

= | equal to | A1=B1 |

> | greater than | A1>B1 |

< | less than | A1<B1 |

>= | greater than or equal to | A1>=B1 |

<= | less than or equal to | A1<=B1 |

<> | not equal to | A1<>B1 |

## Notes

The Excel **IFS **function is the new alternative to NESTED IFs. **IFS **function can execute multiple tests and return a value corresponding to the first TRUE result.

For the sum of values based on conditions, use **SUMIF** or the **SUMIFS** functions.

For the count of values based on conditions, use **COUNTIF **or the **COUNTIFS **functions.

Excel Functions in Alphabetical Order (Complete list)

Complete List of Excel Functions (Category wise)