This guide will explain how to use an IF function with 3 conditions in Excel using three simple and easy methods.
Since it has several built-in functions and tools, Excel is an excellent tool to use for performing complex and difficult data analysis. Moreover, the IF function is a powerful function in Excel that allows us to perform a complex logical test and return certain values based on the evaluation results.
Usually, we would only have one condition to set in the IF function. However, there are times when we would need more than one condition. Luckily, we can input 3 conditions and use a nested IF function. And the nested IF function with 3 conditions would allow us to do complex calculations.
Moreover, there are three simple and easy methods we can do to use an IF function with 3 conditions in Excel. Firstly, we can nest multiple IF functions at the same time. In this case, we would nest 3 IF functions to input our 3 conditions.
Secondly, we can combine the IF function with the AND function to input 3 conditions. Lastly, we can combine the IF function with the OR function to create 3 conditions.
Let’s take a sample scenario wherein we need to use an IF function with 3 conditions in Excel.
Suppose you are creating a sales report containing the different products sold, the monthly sales, and the sales status. To determine the sales status, you followed three conditions.
Let’s say the status would be excellent when the monthly sales are greater than or equal to 1000, good when the monthly sales value is greater than or equal to 500, and average when the monthly sales value is greater than or equal to 250.
Before we move on and explain how to use an IF function with 3 conditions in Excel using three methods, let’s first learn how to write the functions we will be using.
The Anatomy of the AND Function
The syntax or the way we write the AND function is as follows:
=AND(logical1, [logical2],...)
Let’s take apart this formula and understand what each term means:
- = the equal sign is how we activate any function in Excel.
- AND() refers to our
ANDfunction. And this function is used to check whether all arguments are TRUE and returns TRUE if all arguments are TRUE. - logical1 is the only required argument. So this refers to 1 to 255 conditions we want to test that can either be TRUE or FALSE. Furthermore, this can be logical values, arrays, or cell references.
- logical2 is an optional argument. And this acts as a supplement to the conditions we input in the first argument.
The Anatomy of the OR Function
The syntax or the way we write the OR function is as follows:
=OR(logical1, [logical2])
Let’s take apart this formula and understand what each term means:
- = the equal sign is how we begin any function in Excel.
- OR() is our
ORfunction. And this is used to check whether any of the arguments are TRUE. And it will return TRUE or FALSE. Moreover, it will only return FALSE if all arguments are FALSE. - logical1 is the only required argument. So this refers to 1 to 255 conditions that we want to test that can either be TRUE or FALSE.
- logical2 is an optional argument. And this serves as a supplement to the conditions we input in the first argument.
Great! Now we can move on and dive into the three simple and easy methods to use an IF function with 3 conditions in Excel.
How to Use an IF Function with 3 Conditions in Excel Using Nested IF
Firstly, we can create a nested IF function to apply three conditions in our formula. In this case, we will use three IF functions at the same time to input 3 conditions.
When we say a nested function, we are talking about combining more than one function simultaneously. So we will simply be using three IF functions in one formula to input three conditions.
To apply this method, we can simply follow the steps below.
1. Firstly, we can type in the formula “=IF(C2>=1000, “Excellent”, IF(C2>=500, “Good”, IF(C2>=250, “Average”)))”. Lastly, we will press the Enter key to return the result.
2. Secondly, we will drag down the Fill Handle tool to copy and apply the formula to the other cells.
3. And tada! We have successfully used an IF function with 3 conditions using a nested IF formula.
How to Use an IF Function with 3 Conditions in Excel Using AND
Secondly, we can combine the IF function with the AND function to apply 3 conditions in our formula. So the combined IF and AND functions will compare each condition with their text individually and return a value if the conditions do not meet the needed value.
To apply this method in your work, we can simply follow the steps below.
1. Firstly, we can input the formula “=IF(AND(C2>=1000), “Excellent”, IF(AND(C2>=500), “Good”, IF(AND(C2>=250), “Average”, “”)))”. Then, we can press the Enter key to return the result.
2. Secondly, we will drag the Fill Handle tool down to copy and apply the formula to the other cells.
3. And tada! We have successfully used an IF function with 3 conditions in Excel using the AND function.
How to Use an IF Function with 3 Conditions in Excel Using OR
Lastly, we can combine the IF function with the OR function to use it with 3 conditions. So the IF and OR functions will perform a comparison within the 3 conditions we set and return a result according to the values.
To apply this method to your work, we can simply follow the steps below.
1. Firstly, we will type in the formula “=IF(OR(C2>=1000), “Excellent”, IF(AND(C2>=500), “Good”, IF(AND(C2>=250), “Average”, “”)))”. Lastly, we will press the Enter key to return the result.
2. Then, we will drag down the Fill handle tool to copy and apply the formula to the other cells in the column.
3. And tada! We have successfully used an IF function with 3 conditions in Excel using the OR function.
You can make your own copy of the spreadsheet above using the link attached below.
And that’s pretty much it! We have explained how to use an IF function with 3 conditions in Excel using three simple and easy methods. Now you can go ahead and choose any of the three methods to apply to your work whenever you need to perform a logical test using 3 conditions.
Are you interested in learning more about what Excel can do? You can now use the AND function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
