Excel IF Function and IF Statements, with Examples

 

 

Related Link: If…Then…Else Statements (VBA).

 

————————————————————————————————————————

 

Excel IF Function:

The IF function is used to test if a condition is True or False. If a specified condition evaluates to TRUE, the IF function returns one value, and if the condition evaluates to FALSE another value is returned..

 

Syntax: IF(condition, value_if_true, value_if_false)

 

1. condition is a value or expression which is tested to be True or False. Any of the comparison calculation operators can be used here – these operators are: = (Equal to);  > (Greater than); < (Less than); >= (Greater than or equal to); <= (Less than or equal to); <> (Not equal to).

 

2. value_if_true is the value that is returned by the IF function if the condition evaluates to TRUE. This value can also be a formula or function and if left blank, the function returns zero (0).

 

3. value_if_false is the value that is returned by the IF function if the condition evaluates to FALSE. This value can also be a formula or function. If this value is left blank (ie. after value_if_true there is a comma and then blank before the closing paranthesis), the IF function will return zero (0). If this value is not mentioned (ie. after value_if_true there is no comma), the IF function will return FALSE.

 

4. The maximum number of IF functions that can be nested as value_if_true and value_if_false arguments is 64. Excel 2003 only allows 7 levels of nesting of functions, while Excel 2007 allows up to 64. As alternatives to IF function and getting around its nesting limit, you can use the excel LOOKUP, VLOOKUP or HLOOKUP functions, as shown in examples below.

 

5. Excel also has some other functions which can be used to return values based on a condition viz. to count the number of occurrences within a range, use the the COUNTIF AND COUNTIFS functions; to sum a range of values use the SUMIF AND SUMIFS functions.

 

———————————————————————————————————————–

 

Basic IF function

 

 

=IF(A2<40,”Failed”,”Passed”)  [Formula]

 

Formula returns the string “Failed”. (Refer Table 1a)

 

If the number in cell A2 is less than 40, then the formula displays “Failed”. Otherwise, the function displays “Passed”.

 

 

=IF(A4=65,SUM(B2:B6),””)  [Formula]

 

Formula returns the value 19, which is the sum of range B2:B6. (Refer Table 1b)

 

If the number in cell A4 is 65, then the sum of range B2:B6 is calculated. Otherwise, empty text (“”) is returned ().

 

———————————————————————————————————————-

 

Nested IF functions

“Nesting” one IF statement within another increases the number of possible outcomes,  thereby increasing the flexibility of the IF function.

 

 

=IF(A2>84, “Grade A”, IF(A2>=70, “Grade B”, IF(A2>=55, “Grade C”, IF(A2>=40, “GradeD”, “Grade F”))))  [Formula]

 

This formula determines the corresponding grade, of marks mentioned in cell A2. (Refer Table 2a)

 

Formula returns “Grade F”, which corresponds to 36 Marks in cell A2.

 

In this formula, the second IF statement is also the value_if_false argument to the first IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement. For example, if the first logical_test (Marks>84) is TRUE, “A” is returned. If the first logical_test is FALSE, the second IF statement is evaluated, and so on.

 

 

 

=IF(A2>=F3, “Grade A”, IF(A2>=F4, “Grade B”, IF(A2>=F5, “Grade C”, IF(A2>=F6, “GradeD”, “Grade F”))))  [Formula]

 

This formula determines the corresponding grade of marks mentioned in cell A2. (Refer Table 2b)

 

Formula returns “Grade F”, which corresponds to 36 Marks in cell A2.

 

This is an alternate to the preceding formula. Note the linkage to cells F3 to F6.

 

———————————————————————————————————————–

 

Using IF function with Excel Logical functions

Excel recognizes the Boolean values True and False, and uses Boolean Logic in its  logical operations such as the AND(), OR(), NOT() functions …. for example, the output of AND function is TRUE only if all of the inputs are TRUE.

 

 

AND Function

=IF(AND(A5>=F5, A5<F4), “Grade C”, “Other Grade”)  [Formula]

 

This formula determines if the corresponding grade, of marks mentioned in cell A5, is “Grade C” or “Other Grade”. (Refer Table 3a)

 

Formula returns “Other Grade”, because marks in cell A5 are 77 which do not correspond to the Grade C range of >=55 AND less than 70.

 

The AND() function returns TRUE only if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.

 

 

 

OR Function

=IF(OR(A2>80, A3>80, A4>80, A5>80, A6>80), “Atleast One”, “None”)  [Formula]

 

This formula returns “Atleast One” in case minimum one cell in the range “A2:A6” contains marks > 80, else returns “None”. In the present case, cell A6 contains 88, hence the formula returns “Atleast One”. (Refer Table 3b)

 

The OR() function returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.

 

 

 

NOT Function

=IF(NOT(A4<40),”Passed”,”Failed”)  [Formula]

 

This formula returns “Passed” in case the value in cell A4 is NOT less than 40 (ie. reverses the argument “A4<40”), else returns “Passed”. In the present case, cell A4 contains 65, hence the formula returns “Passed”. (Refer Table 3c)

 

The NOT() function reverses the value of its argument. Use NOT when you want to make sure a value is not equal to one particular value.

 

———————————————————————————————————————-

 

Alternatives to IF Function and Getting Around its Nesting Limit

 

 

Using LOOKUP function in place of IF function

=LOOKUP(A2, {0,40,55, 70,85}, {“Grade F”, “Grade D”, “Grade C”, “Grade B”, “Grade A”})  [Formula]

 

This formula determines the grade of marks mentioned in cell A2. (Refer Table 4)

 

Formula returns “Grade F”, which corresponds to 36 Marks in cell A2.

 

In this formula, the LOOKUP function is used instead of the IF function. For a larger number of conditions to test you may find this easier to read and maintain, besides getting around the nesting limit of IF Function.

 

Using Boolean Logic in place of IF function

A Boolean value is one that can be expressed in only one of two values, as either TRUE or as FALSE. A non-numeric (BOOLEAN) value is coerced into a numeric value by Excel, by using arithmetic operations. TRUE will become 1 and FALSE will become 0 when used in multiplication, addition, … (TRUE + 2 = 3, whereas FALSE + 2 = 2).

 

=IF(A10=”Pear” , 15, (IF(A10=”Orange”, 25, (IF(A10=”Apple” , 35,0))))) is a nested IF function. This formula will return the value 25 if the cell A10 mentions “Orange”. This can be replaced by Boolean Logic, as follows:

 

=(A10=”Pear” )*15+ (A10=”Orange” )*25+ (A10=”Apple” )*35. Explanation: Explanation: =(A10=”Pear” ) part of the formula returns “FALSE” which evaluates to 0; =(A10=”Orange” ) returns “TRUE” which evaluates to 1; =(A10=”Apple” ) returns “FALSE” which evaluates to 0.

 

Breaking the formula will look like: 0*15 + 1*25 + 0*35 = 25.

 

It may be noted that the comparison (A10=”Orange”) is not case sensitive. It will evaluate “Orange” the same as “orange”, or “ORANge” …. To make this case-sensitive, use =EXACT(A10,”Orange”). In this case, the formula will get changed to:

 

=EXACT(A10, “Pear”)*15 + EXACT(A10, “Orange”)*25 + EXACT(A10,”Apple”)*35

 

Using these formula(s) will also get you around the nesting limit of IF function.

 

———————————————————————————————————————–

 

Relating IF Function with Excel Information Functions viz. IFERROR, ISERROR, ISBLANK, ISNUMBER, ISTEXT, CELL, ….

Excel Information functions give information about the type of value or data in a cell, viz. whether the data is a string or a number, if the cell is empty, the formatting applied to the cell, and so on.

 

ISERROR function vis-à-vis ISERROR

 

 

Closely related to the IF() function is the IFERROR() function. Use the IFERROR function to trap and handle errors in a formula. IFERROR(value,value_if_error): the function returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. The following error types are evaluated/covered: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

 

– Example: =IFERROR(1/F7, 0). In this case dividing 1 by value in cell F7 (ie. 1/0) will return a #DIV/0! Error. Using IFERROR, the value returned is 0. (Refer Table 5a)

 

You can also use the ISERROR formula in combination with IF Function, to trap errors.

–  Example: =IF(ISERROR(1/F7), 0, (1/F7)). In this formula, IF dividing 1 by value in cell F7 (ie. 1/0) evaluates to an error (#DIV/0!) the IF function returns the value 0, else it returns the result of the division. (Refer Table 5b)

 

 

 

– It may be noted that using the formula “=IF(F7=0,0,(1/F7))” may be more efficient than using the ISERROR combinattion “=IF(ISERROR(1/F7), 0, (1/F7))”, but you may come across instances when you might not get the chance to pre-test conditions (like “F7=0”) in the IF statements as in the present case. (Refer Table 5c)

 

 

 

ISBLANK function

=IF(NOT(ISBLANK(A6)), B6, 0)  [Formula]

 

This formula returns the value in cell B6 (ie. 2) if cell A6 is NOT blank {ie. reverses the argument “ISBLANK(A6)”}, else returns 0. In the present case, cell A6 contains 88, hence the formula returns 2 (value in cell B6). {Refer Table 6}

 

———————————————————————————————————————-

 

Using IF function with Array formulas

 

 

{=MAX(IF(MOD(A2:A6, 2)<>0, A2:A6))}  [Formula]

 

Enter =MAX(IF(MOD(A2:A6,2)<>0,A2:A6)) as an array formula: type the formula in the cell and then press CTRL+SHIFT+ENTER instead of just ENTER. Excel will automatically display the formula enclosed in braces { }. (Refer Table 7)

 

The formula returns the Largest value in the range “A2:A6”, taking into account the values of only those cells which have ODD numbers. In the present case, the formula evaluates the largest value in cells with ODD numbers ie. in cells A4 & A5, and returns the value in cell A5 which is 77.

 

Using this array formula with COUNT, SUM & AVERAGE functions will give similar results as the excel functions COUNTIFS, SUMIFS & AVERAGEIF. It may be noted that some functions like COUNTIFS, SUMIFS, AVERAGEIF, … were introduced only in Excel 2007 and this array formula will be particularly useful for use in earlier versions of Excel and also with functions like MAX, MIN, … in which cases excel formulas like MAXIF, MINIF, … do not exist.