How to Create a Nested Formula with IF and AND Functions in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Nested Formula in Excel IF AND

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.

Nested Formula in Excel IF AND

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.

Nested Formula in Excel IF AND

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.

Nested Formula in Excel IF AND

  • 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.

Nested Formula in Excel IF AND

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.

Nested Formula in Excel IF AND

  • 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.


Related Articles

Naimul Hasan Arif
Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo