Excel IF Function: A Comprehensive Guide for Beginners

How Do You Use the Excel If Function?

The IF function is a built-in Excel function that checks for a condition. If the condition is met, the true value is returned; otherwise, the false value is returned. The syntax is as follows:

Let’s look at an example to understand this better. 

Here, we are using data from inventory management to demonstrate the usage of the IF function.

The formula  IF(G2="Pending","YES","NO")  checks if the status of the order is still pending. If it is, the TRUE part of the statement executes (i.e., "YES"), otherwise,the FALSE value executes, displaying "NO."

Where Else Can This Function Be Used?

The IF function can be used to perform calculations based on a condition. Here are a couple of formula examples:

1. Once the function checks for a condition, instead of returning values (true_value or false_value), it can perform a calculation and return the resultant value.

• IF(C2>20, C2*0.05, C2*0.07)

The formula checks if the value in cell C2 is greater than 20. If TRUE, the value in cell C2 is multiplied with 0.05 (or five percent). If FALSE, it is multiplied with 0.07 (or seven percent.)

2. The condition inside IF can be any function. For example ISBLANK, ISNUMBER, AVERAGE, SUM, and so on. 

Look at the formula below to check for blank cells by using the function ISBLANK along with IF.

• IF(ISBLANK(C2), “TRUE”, “FALSE”)

 The formula returns TRUE if cell C2 is blank and FALSE otherwise. 

What is the IFNA Function?

The IFNA function belongs to the group of error-checking functions in Excel. This function helps in handling #N/A errors.

The syntax is as follows:

The formula executes first, and the IFNA function checks if the result is an #N/A error. It is a "no value available" error and occurs when a value cannot be found.

In that case, the value_if_error is returned. If the #N/A error doesn't occur, the result of the formula will be returned.

Let’s consider the following example :

• To understand the IFNA function, let’s first understand the functions that generate the #N/A error. These functions include VLOOKUP, HLOOKUP, etc. 
• VLOOKUP is a function that helps look up a value from a table vertically. The syntax for this function is: VLOOKUP (value, table, col_index, [range_lookup])

As we see, the VLOOKUP function generates an #N/A error since the value is not found, so let’s attempt to use it with IFNA to trap the #N/A error.

We will use the following formula: IFNA(VLOOKUP(B9,A2:F5,6,TRUE)," NOT FOUND")

• The IFNA function first executes the VLOOKUP, which generates an #N/A error if the value is not found. Therefore,we will use the IFNA function along with VLOOKUP. 
• In case of an #N/A error, the alternate value (value_if_error) executes and displays.

Since the value is unavailable in this example, we’ll receive a “NOT FOUND” message.

Master Data Science and Unlock Top-Tier Roles

With the Data Scientist Master's ProgramStart Learning

What is the IFERROR Function?

IFERROR also belongs to a group of error-checking functions in Excel. It is a variation of the IF function. The IFERROR functions and helps to recognize errors and handles them efficiently.

The syntax is as follows:

This function executes the formula and checks if the result contains an error. In case there is an error, the value_if_error is returned.

The demo shown below provides a clearer understanding of the IFERROR function.

In this example, we use the formula IFERROR(C1/C2," Error in calculation")

It first executes C1/C2. If this results in any error, the alternate value will be displayed. (i.e., "Error in calculation").

How to Use Nested IF in Excel

The nested IF function can be used to test multiple conditions. It consists of one IF inside another IF function. The syntax is:

• The first IF function executes, and the condition1 is evaluated
• If condition1 is true, then the true_value1 is executed.
• The false_value1 is embedded in the following IF function. Hence, if the condition1 is false, the following IF function will execute
• Condition2 is verified, andtrue_value2 is returned if condition2 is true. Otherwise, false_value2 is returned.

You can continue nesting up to seven IF functions if needed.

The following demo illustrates how nested IF works:

IF(B4=" Beauty"," 7%", IF(B4=" Electronics", IF(B4=" Sports"," 4%"," Not applicable")))

• The table on the right shows the tax percentage for each product line. We formulate this using the nested IF function. 
• We check if the cell value is "Beauty" which returns "7%" if TRUE.
•  If FALSE, the cell value is checked for "Electronics," which displays "5%" if TRUE. 
• Otherwise, it will check again to verify if the cell value is "Sports." This time, "4%" is displayed if TRUE, and "Not Applicable" if FALSE. 

The IFS Function 

The IFS function eliminates the complexity of using nested IF in Excel. It uses a shorter and simpler syntax that targets multiple criteria.

This function is available only on Excel 2019 and Excel for Office 365.

The syntax for IFS:

The IFS function consists of a condition-value pair. It tests the conditions in order and returns a value that corresponds to the first TRUE result. This function can test up to 127 conditions.

For example:

=IFS(C2="Grocery","5%",C2="Electronics","12%",C2="Automobiles","28%")

• In this example, the value of C2 is checked. 
• If C2 contains the value "Grocery," the tax rate will be displayed as "5%". 
• Otherwise, the value in C2 is checked again to verify if it contains "Electronics." If TRUE, the tax rate will be "12%".
• Otherwise, the C2 value is finally checked with "Automobiles." If this condition is met, "18%" isdisplayed. 
• If none of the conditions are met, this function will return an #N/A error.

More About Excel IF

Combining the IF function with other Excel functions will improve your productivity and help you perform complex calculations.

Here are a few examples that show how to combine the Excel IF function with others:

IF Function With AND

The syntax for this will be:

This function will check if all \conditions (condition1, condition2, etc.) are satisfied, and only then the true_value execute. Even if one condition is not met, the false_value will execute.

For example:

IF(AND(B2>5,C2<5),"Excellent","Fair")

In this example, we are calculating the ratings with respect to shipment, cost, and delivery times. The only combination that will receive an excellent rating is both a shipping cost below five dollars, in addition to a delivery time of fewer than five days. Otherwise, it will only receive a “fair” rating.   

Become the Highest Paid Data Science Expert

With Our Best-in-class Data Science ProgramExplore Now

IF Function With OR

The syntax for this will be:

If at least one condition is met out of all the conditions (condition1, condition2, and so on), then the true_value will execute. If all the conditions are FALSE, then the false_value executes.

For example:

IF(OR(B2=" Fruits",B2="Vegetables")," Grocery","Others")

• In this example, we find the product category. 
• We use the IF function with OR to check if the product is categorized as either a fruit or vegetable. 
•  If so, it is categorized as "Grocery."  Otherwise, it displays as "Others."

Conclusion

We hope this article helped to provide a basic introduction to the Excel IF function. You can improve your Excel skills with hands-on practice by working with more data, and you’ll be on your way to becoming an Excel pro. You can enhance your Excel skills by enrolling in the Caltech Post Graduate Program in Data Science from Simplilearn.

If you have any questions about this article, please leave them in the comments section, and our experts will get back to you as soon as possible.