I’m sure we do use the word “If” quite commonly in conversations in our lives. It usually indicates a condition that has to be satisfied before something can happen. Similarly, such a statement can be expressed using an IF function in Excel too! That’s why I’ve done some sourcing on behalf of you the simplest and quickest explanation on how you can use this IF function in Excel. This post should provide a basic introduction to the function and how you can use it too.
How is the IF function in Excel used?
Put simply, the IF function works by taking in 3 different inputs – the logical test, the value to display if true and the value to display if false. The function takes a number or text and checks this against the test. Only either true or false can be displayed. If the test results in true, the value if true will be displayed. On the other hand, if the test results in false, the value of false will be displayed. Depending on the logical test, the displayed output can vary.
However, most Excel users tend to overlook the different uses for the IF function. In fact, the IF function can provide useful calculations using its simple logical concepts.
What is the logical concept behind the IF function?
Naturally, when hearing the word “If” and using in sentences you are already using a logical concept in your mind. Unknowingly, we all tend to put situations to the test: “Is this true?”, “Is this false?”. This is somewhat intuitive to us as humans but Excel, being a computer program, has to use this simple logical true/false checking mechanism to replicate this concept through the IF function.
As shown in the simple flowchart diagram above, there are two pathways where a value can take, either a true result or the false result.
The Logical Test
The logical test in this function is essentially just a simple true/false test. It compares and checks if a certain value (as chosen by you) matches criteria. The result is two-way, either a true result or a false result. This concept of logic helps the formula decide what to display – the value if true or the value if false.
=IF (logical_test, [value_if_true], [value_if_false])
The cell block above shows the syntax of the IF function, which basically means how the function is structured. The formula begins with an equal sign followed by “IF” and 3 arguments enclosed in brackets.
logical_test value_if_true value_if_false
If you’re new to Excel or programming concepts, the concept of arguments may seem complicated and confusing to you. But not to worry, the arguments of each function are simply what you have to key into the formula bar in order for it to work.
Most arguments are required, meaning they are a must to key in while some are optional, meaning they can be left blank. Arguments are usually separated from each other using a comma “,”.
The 3 different arguments listed above are the inputs a user has to provide when filling up the formula. The logical test is typically used with an equals “=” sign, to indicate the condition. Its value is then evaluated and given the true/false outcome.
The value_if_true argument is an optional argument that returns the result when evaluated true. The value_if_false argument is also an optional argument but returns the result when evaluated false.
When is the IF function used?
The IF function is typically used when a user needs to check if the value in a cell matches that of the condition (one that’s made by the user). The function then displays one of two possible values, according to the user’s condition.
Here are some possible use cases for the IF function:
- Displaying a Yes/No result based on whether a checkbox is ticked for an inventory list
- Tracking personal expenses to see if they have reached a certain limit
IF Combined with AND & OR
Being a versatile logical function, the IF function can be complemented with other functions within Excel. Some of these include the AND function and the OR function. For example, when used in combination, we can find out if a certain number matches the multiple criteria that we want and show us a result based on the multiple criteria that we have set.
The AND function can be used to combined multiple conditions for the logical test to fulfill. As the name of the function goes, anything within the parentheses of this function will combined in an additive manner.
=IF(AND(A3 > 15, A3 < 20),"Yes","No")
Meaning: If Cell A3 is larger than 15 and lesser than 20, display “Yes”, else display “No”.
The OR function is also a great option for combinations with the IF function. Similar to what we did with the AND function, the OR function is placed within the logical test argument. It is used to show the either/or style of evaluation.
=IF(OR(A3 > 15, A3 < 20),"Yes","No")
Meaning: If Cell A3 is larger than 15 or lesser than 20, display “Yes”, else display “No”.
Nested If Formulas
Nested IF functions are formulas that have multiple layers of IF functions nested within each other to allow for more conditional testing and more specific output. For a nested IF formula, a minimum of one IF function should be nested into the parentheses of the other.
The term nested is used because the innermost functions are put inside the outer-few layers like eggs in a nest. In many ways, this may seem like an onion with multiple layers. Within each layer, we have another layer. For the Nested IF formulas, a maximum of 7 layers is allowed in Excel. Now that’s a lot of layers.
Which IF function goes first in Nested IF formulas?
The innermost function is computed first before moving outwards, the following outer functions then take into consideration of the output of the previous function.
=IF(=IF(OR(A3 > 15, A3 < 20),"Yes","No"), "Within range", "Outside range"
This example shown above is a nested IF function that has a total of 2 IF statements that build upon the logical concepts of each other.
The IF function shown in orange is the inner function, and therefore is processed in Excel first. The IF function shown in blue text is the outer function and is processed second. The second function “wraps” itself around the first. This concept of wrapping allows us the flexibility and functionality of 2 IF statements.
As mentioned in the arguments section above, the logical test argument usually includes an equal sign. However, the equals sign is one of several logical operators. Logical operators are part of our lives more than you think!
For example, we think about them when considering the prices of items – which price is greater than which. Another typical example is discount minimum purchases, where we have to consider buying an item with greater than a certain amount to qualify for a discount.
Similarly, these concepts are built into Excel through operators shown in symbols. Apart from the equals sign, there are several other mathematical operators commonly used among Excel users as well. The summary table shows the possible operators and their respective meanings.
|>=||greater than or equal to|
|<=||less than or equal to|
|<>||not equal to|
With the versatility of these operators, we can create unique conditions in our logical test to create and fulfill a specific criteria that we want.
If functions with multiple conditions
When used correctly and in combination with other functions, the IF function can perform some complicated computations. Multiple IF statements can be built together up to 7 times, which allows for building up a strong logical structure similar to a flowchart! These nested IF functions can be used together with the AND and OR functions to build complex logical calculations.
In conclusion, the IF statement can be a simple and plain function but that’s what makes it all the more powerful! It is essentially a crucial building block that forms the foundation for more complicated and versatile use cases. Hopefully, this quick introductory article provided a simple and friendly approach to the IF function.
If you would like to read more articles on Excel, you can access them here. Thanks for reading!
My Favorite Learning Resources:
My Recommended Learning Platforms!
|Learning Platform||What’s Good About the Platform?|
|1||Coursera||Certificates are offered by popular learning institutes and companies like Google & IBM|
|2||DataCamp||Comes with an integrated coding platform, great for beginners!|
|3||Pluralsight||Strong focus on data skills, taught by industry experts|
|4||Stratascratch||Learn faster by doing real interview coding practices for data science|
|5||Udacity||High-quality, comprehensive courses|
My Recommended Online Courses + Books!
|1||Data Analytics||Google Data Analytics Professional Certificate||–|
|2||Data Science||IBM Data Science Professional Certificate||–|
|3||Excel||Excel Skills for Business Specialization||–|
|4||Python||Python for Everybody Specialization||Python for Data Analysis|
|5||SQL||Introduction to SQL||SQL: The Ultimate Beginners Guide: Learn SQL Today|
|6||Tableau||Data Visualization with Tableau||Practical Tableau|
|7||Power BI||Getting Started with Power BI Desktop||Beginning Microsoft Power BI|
|8||R Programming||Data Science: Foundations using R Specialization||Learning R|
|9||Data Visualization||–||Big Book of Dashboards|