Do you want to learn how to use Excel’s IF function, including how to consider multiple conditions? If yes, then you have come to the right place! The IF function is one of the most commonly used functions in Excel, and it is essential for data analysis and decision making. In this post, we’ll walk through the basics and then proceed to examples that demonstrate how to analyze multiple conditions or tests.
Let’s get to it.
Video
IF Function Overview
The IF function checks if a condition is true or false and returns a value based on the result. The IF function has three arguments: the logical_test, the value_if_true, and the value_if_false:
=IF(logical_test, value_if_true, value_if_false)
For example, you can use the IF function to check if a student has passed or failed an exam based on their score. If the student’s score is greater than or equal to 60, they have passed the exam, and the function will return “Pass.” If the student’s score is less than 60, they have failed the exam, and the function will return “Fail.” Assuming the score is stored in cell A1, the formula to accomplish this is:
=IF(A1>=60, "Pass", "Fail")
- The logical_test is: A1>=60
- The value_if_true is: “Pass”
- The value_if_false is: “Fail”
The IF function in Excel allows us to perform different calculations or return different results based on specific conditions. In this tutorial, we’ll cover the basics of the IF function and explore a few advanced examples as well.
Basic Example
Let’s say we want to calculate a bonus amount for employees based on their eligibility. If an employee is eligible, their bonus is $500, and if not, their bonus is $0. We want to write a formula in column D and fill that formula down:
We can accomplish this using the following formula in cell D13:
=IF(C13="Y",500,0)
Here, the IF function has three arguments: the test, the value if the test is true, and the value if the test is false. The test checks whether the value in cell A2 is equal to “Y”. If the test is true, the function returns the value 500. If the test is false, the function returns 0.
We fill the formula down, and bam:
Now that we are warmed up, let’s take a look at a few additional examples.
Additional Examples
Exercise 1
Let’s say we want to calculate a bonus based on multiple conditions. If an employee is not eligible, their bonus is $0. If they are eligible, we check whether they are vested or not. If they are vested, their bonus is $500. Otherwise, their bonus is $0.
We can use the following nested IF function to accomplish this:
=IF(C14="N",0,IF(D14="Y",500,0))
Here, the IF function is nested within another IF function. The first test checks whether the value in cell C14 is equal to “N”. If the test is true, the function returns 0. If the test is false, the second test checks whether the value in cell D14 is equal to “Y”. If the test is true, the function returns 500. If the test is false, the function returns 0.
Exercise 2
We can also use the OR and AND functions to check multiple conditions. Let’s say we want to calculate a bonus based on whether an employee is eligible or not and whether they have completed a certain training program or not. If an employee is not eligible or has not completed the training program, their bonus is $0. Otherwise, their bonus is $500. We can use the following IF function with the OR function to accomplish this:
=IF(OR(A2="N",B2="N"),0,500)
Here, the OR function checks whether either the value in cell A2 is equal to “N” or the value in cell B2 is equal to “N”. If either test is true, the OR function returns true and the IF function returns 0. If both OR tests are false, then OR returns false and IF returns 500.
Exercise 3
We can also use the AND function to check multiple conditions. Let’s say we want to calculate a bonus based on whether an employee is eligible and has completed a certain training program. If an employee is eligible and has completed the training program, their bonus is $500. Otherwise, their bonus is $0. We can use the following IF function with the AND function to accomplish this:
=IF(AND(A2="Y",B2="Y"),500,0)
Here, the AND function checks whether both the value in cell A2 is equal to “Y” and the value in cell B2 is equal to “Y”. If both AND tests are true, AND returns true and the IF function returns 500. If either AND test is false, then AND returns false and IF returns 0.
Exercise 4
Okay, here we’re going to use the IFS function. Now, the IFS function is designed to look at a string of conditions, and whichever one hits true first is the one that’s returned. Here’s how we set it up:
=IFS(C14="N",0, D14="N",0, TRUE,500)
Basically, if C14 is equal to “N” then we return zero. If that’s not true, then it’s going to look at the next argument. If D14 is equal to “N” then we return zero. If that’s not true, we look at the next argument. The next argument is TRUE, which always returns true, so the function returns 500. Basically, that final pair is the equivalent of saying “if none of the previous conditions are true, return this.”
So, it’s just going in order. Once one of the logical tests returns true, it stops and ignores the remaining tests. And that’s how you can use the IFS function to handle multiple conditions in Excel.
Frequently Asked Questions
How do you do an IF/THEN formula in Excel?
To create a formula that uses the IF function in Excel, follow these simple steps:
- Open a new Excel worksheet and select a cell where you want to insert the formula.
- Type =IF( in the cell and then enter the logical test or condition you want to check. For example, =IF(A1>50, “Pass”, “Fail”) will check if the value in cell A1 is greater than 50. If it is, the formula will return “Pass,” and if it is not, the formula will return “Fail.”
- Close the parentheses and press Enter. The result of the formula will be displayed in the cell.
How do you put 2 conditions in an Excel IF function?
To put two conditions in an IF formula in Excel, you can use the AND or OR function along with the IF function. For example, =IF(AND(A1>50, B1>60), “Pass”, “Fail”) will check if the value in cell A1 is greater than 50 and the value in cell B1 is greater than 60. If both conditions are true, the formula will return “Pass,” and if either condition is false, the formula will return “Fail.”
How do you put 3 conditions in if Excel?
To put three conditions in an IF formula in Excel, you can use nested IF functions, or use the IFS function instead. To nest multiple IF functions, use the following format =IF(logical_test1, IF(logical_test2, IF(logical_test3,…),…),…). To use the IFS function instead, use the following format =IFS(logical_test1, value_if_true, logical_test2, value_if_true, logical_test3, value_if_true, …).
What is an IF THEN formula?
An IF/THEN formula in Excel is another term for the IF function. The IF function checks if a condition is true or false and returns a value based on the result.
Can I write 2 conditions in an IF statement?
Yes, you can write two conditions in an IF statement using the AND or OR function. You can also use nested IF functions or the IFS function to check multiple conditions.
What are the 3 arguments of the IF function?
The three arguments of the IF function in Excel are:
- Logical test: the condition you want to check
- Value_if_true: the value to return if the logical test is true
- Value_if_false: the value to return if the logical test
Conclusion
I hope that this post provided useful information for how to use the IF function Excel, and, a few alternatives for analyzing multiple conditions. If you have any suggestions, alternatives, or things you’d recommend, please share by posting a comment below … thanks!
Sample file
ExcelIF_downloadDownload
