Naimul Hasan Arif
Human being likes to get everything with less effort or without effort. We prefer shortcuts in almost every aspect of our life. It would be much preferable for us to do a calculation in a single complicated formula rather than too many simple formulas. Despite using IF and AND functions separately, we can create nested formula with IF and AND functions in Excel. I am going to discuss it in this article.
Download Practice Workbook
3 Ways to Create a Nested Formula with IF and AND Functions in Excel
1. Single IF and AND Functions in Nested Formula
We can create a nested formula with a single IF and AND function rather than employing them in two different formulas.
For more clarification, I have taken a Dataset of an industry with Particulars and Category columns. We will try to find out with a single IF and AND Function in Nested Formula whether the category listed here is credit or debit. You can follow the procedures listed below to do so.
Steps:
- Input the following formula in cell
=IF(AND(C5="Expense"), "DEBIT","CREDIT")Here, AND function firstly evaluates the value inside it whether it is true or false. Based on it, the IF function will give the output.
- Now, press the ENTER button to have the output.
- Finally, use Fill Handle to AutoFill the rest cells.
Read More: How to Use Nested IF and SUM Formula in Excel (2 Easy Methods)
2. Nested Formula with Multiple IF and AND Functions
Multiple IF and AND functions can also be used to create a nested formula.
It will be more clear to us if we can concentrate on the following descriptions. Here, we are going to use a Dataset to evaluate a student’s performance with his obtained marks in the examination. I have selected cell D5 to apply the nested formula.
Steps:
- First of all, apply the following formula in cell D5:
=IF(AND(C5>=80),"Excellent",IF(AND(C5>=40),"Average","Poor"))Here, AND function verifies whether the value in cell C5 meets the conditions inside the parenthesis. If the value in cell C5 is greater or equal to 80, it will show the output Excellent. Again, If the value in cell C5 is greater or equal to 40, it will show the output Average. Otherwise, it will show Poor.
- Hit the ENTER button.
- Now, AutoFill the rests.
Thus, we can create a nested formula with multiple IF and AND functions.
Read More: How to Use Nested IF Function in Excel (6 Ideal Examples)
3. Nested IF and AND Formula with OR Function
In order to add more conditions in the nested formula of IF and AND, we can add OR function too.
I am going to simplify the whole procedure with a Dataset of football players’ contributions to goals and assists in the top leagues around the world. Based on their statistics, I am going to categorize them as Extra ordinary (Extraordinary) or Average players. I have selected cell E5 to employ my formula.
Steps:
- Input the following formula in cell E5.
=IF(OR(AND(C5>35, D5>25), AND(C5>50, D5>15)), "Extra ordinary", "Average" )Here, I applied IF, AND, and OR functions in the same formula. AND function creates two sets. One is with 35 goals and 25 assists, another one is with 50 goals and 15 assists. OR function checks whether the player has fulfilled one of the sets that are declared with AND functions to recognize him as Extra ordinary. IF function determines whether the player is Extra ordinary or Average.
- Now, press ENTER to verify the formula.
- Fill the blank cells with AutoFill.
Read More: How to Use Nested IF Else in Excel Formula (4 Suitable Ways)
Conclusion
In this article, I have tried to create a nested formula with IF and AND functions in Excel. I hope it will be helpful for all. For any further questions, comment below. For more information regarding Excel, you can visit our Exceldemy site.
