In Excel, you will find many features used for different computational tasks. Similarly, the IF function is also important because it helps in performing lookups to group data, comparing data, and making interactive reports. Let’s understand the basic notion of the IF function in this post.
The value of the IF function would be as:
=IF(logical_test, [value_if_true], [value_if_false])
We have 3 arguments for the IF function:
Logical test: In this argument, we get to know if the condition is met and the data is compared.
Value if true: If the condition is met as per the above argument, Excel has to return a specific value.
Value if false: On the other side, if the condition is not met, this argument simply clarifies which value will be returned.
So, basically, the main query here is how you can use an IF function with 3 conditions in Excel. Using the below-given formulas, you can use the IF function with 3 conditions in Excel:
Method 1: Nested IF Function
=IF(C2<15, “Bad”, IF(C2<20, “OK”, IF(C2<25, “Good”, “Great”))
Method 2: IF Function with AND Logic
=IF(AND(A2=”Mavs”, B2=”Guard”, C2>25), “Yes”, “No”)
Method 3: IF Function with OR Logic
=IF(OR(A2=”Mavs”, B2=”Guard”, C2>25), “Yes”, “No”)
Now, let’s have some examples to understand clearly how you can use each formula in Excel”
Example 1: Nested IF Function
In the Points column, when you enter the following formula in cell D2, it will return a value based on the value for each player.
The formula is: =IF(C2<15, “Bad”, IF(C2<20, “OK”, IF(C2<25, “Good”, “Great”)))
In column D, now you can fill in this formula for each remaining cell.
This formula can do things such as:
In the Points column, if the value is not more than 15, it will return Bad.
On the other hand, if the value in the Points column is not more than 20, it will return OK.
Whereas, if the value is not more than 25 in the Points column, it will return Good.
Or else, it will return Great.
Example 2: IF Function with AND Logic
If 3 conditions are met for a certain player, typing the formula in cell D2 will return “Yes” and it will be “No” if at least one condition is not met:
=IF(AND(A2=”Mavs”, B2=”Guard”, C2>25), “Yes”, “No”)
Now, you can fill in the formula for each remaining cell in column D:
This formula is capable of performing these actions:
In the Team column, if the value is “Mavs” and in the Position column the value is “Guard” and in the Points column the value is not less than 25, it will return Yes.
On the other hand, if at least one condition is not met the return will be No.
Example 3: IF Function with OR Logic
In Cell D2, type this formula to return “Yes” if any of the conditions are met for a certain player or “No” if none of the conditions are met:
=IF(OR(A2=”Mavs”, B2=”Guard”, C2>25), “Yes”, “No”)
Now you can fill this formula to each remaining cell in column D:
This formula is capable of performing these actions:
If the value in the Team column is “Mavs”, and in the Position column the value is “Guard”, and if the value is not less than 25 in the Points column, the return will be Yes.
Or else, it will be No, if none of the conditions are met.
How to Use Multiple If Condition in Excel
Apart from the above IF condition in Excel with 3 conditions, let’s have another explanation of Excel if formula with 3 conditions.
Excel IFS Function:
The IFS function simplifies multiple conditional statements by evaluating them in a more structured way. Instead of nesting, you can list your conditions and corresponding values in a clear manner:
=IFS(condition1, value1, condition2, value2, condition3, value3, …)
In Excel IF function 3 conditions, you will check each condition in order and return the corresponding value when it finds the first true condition.
SWITCH Function:
The SWITCH function is another powerful way to handle multiple conditions. It works like a case statement in programming languages. Here’s an example:
=SWITCH(condition, value1, result1, value2, result2, default)
It compares the given condition to a list of values and returns the corresponding result when a match is found.
Real-World Examples
Now, let’s look at a couple of practical examples to demonstrate the use of multiple IF conditions in Excel.
Example 1: Sales Commission Calculation
Suppose you need to calculate sales commissions based on different tiers. You can use the IFS function to achieve this:
=IFS(Sales >= 10000, Sales * 0.1, Sales >= 5000, Sales * 0.07, Sales >= 1000, Sales * 0.05, TRUE, 0)
In this example, if the sales amount is over $10,000, the commission is 10% of the sales. If it’s between $5,000 and $10,000, the commission is 7%. If it’s between $1,000 and $5,000, the commission is 5%. Otherwise, the commission is 0.
Did you get the idea about how to use IF statement in Excel?
Let’s have another example:
Example 2: Letter Grade Calculation
You can use nested IF functions to calculate letter grades based on a student’s score:
=IF(Score >= 90, “A”, IF(Score >= 80, “B”, IF(Score >= 70, “C”, IF(Score >= 60, “D”, “F”))))
In this example, if the student’s score is 90 or above, they receive an “A.” The nesting of IF with multiple conditions allows you to handle different grade ranges. So, this is how you get another well-defined explanation of multiple if function Excel.
To Sum Up
So, this is it. Now, you understand how to use an IF function with 3 conditions in Excel. Keep practicing each tool and enjoy mastering different features.
