MS Excel is a powerful tool that helps improve a user's productivity while working on data. This is due to the extensive range of functions present in Excel. The Excel IF function is one such function that makes it easy to classify data as per a specified condition. There are five variations to the Excel IF function that we will be discussing.

In this article, you will learn:

- How do you use the Excel IF function?
- What is the IFNA function?
- What is the IFERROR function?
- How to use nested IF in Excel
- The IFS function
- More about Excel IF

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:

IF(condition, true_value,false_value) |

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."

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.Â

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:

IFNA(formula, value_if_error) |

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.

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:

IFERROR(formula, value_if_error) |

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").

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

IF( condition1, true_value1, IF( condition2, true_value2, false_value2)) |

- 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 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:

IFS( condition1, value1, [condition2, value2],â€¦) |

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.

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:

The syntax for this will be:

IF(AND(condition1, condition2,â€¦), true_value, false_value) |

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.Â Â Â

The syntax for this will be:

IF(OR(condition1, condition2,â€¦), true_value, false_value) |

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."

Gain expertise in the latest Business analytics tools and techniques with theÂ Business Analyst Master's Program. Enroll now!

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 Business Analytics Certification Course with Excel 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.Â

Name | Date | Place | |
---|---|---|---|

Post Graduate Program in Business Analysis | Cohort starts on 27th Jun 2021, Weekend batch | Your City | View Details |

Post Graduate Program in Business Analysis | Cohort starts on 3rd Jul 2021, Weekend batch | Chicago | View Details |

Post Graduate Program in Business Analysis | Cohort starts on 11th Jul 2021, Weekend batch | Houston | View Details |

Simplilearn is one of the worldâ€™s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

Post Graduate Program in Business Analysis

2183 Learners

Lifetime Access*

Business Analyst

10537 Learners

Lifetime Access*

Business Analytics with Excel

20099 Learners

Lifetime Access*

*Lifetime access to high-quality, self-paced e-learning content.

Explore Category- Ebook
Business Intelligence Career Guide: Your Complete Guide to Becoming a Business Analyst

- Article
Introducing the Post Graduate Program in Cyber Security

- Video Tutorial
The Top 10 Skills You Need to Have to Be a Business Analyst

- Ebook
The Ultimate Guide to Top Front End and Back End Programming Languages for 2021

- Article
Introducing the Post Graduate Program in Lean Six Sigma

- Video Tutorial
A Look at the Top 5 Programming Languages for Hacking

prevNext

- Disclaimer
- PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.