# How to Use the Nested IF and AND Functions in Excel (4 Examples)

## Dataset Overview

Letâ€™s start by introducing our dataset. On the sheet, we have the names of students, their CGPA, and the number of credits theyâ€™ve earned. Weâ€™ll be using nested IF and AND functions in Excel to apply multiple conditions to this dataset.

### Example 1 – Applying AND Function Nested in IF in Excel

• Select cell D5.
• In that cell, insert the following formula:
`=IF(AND(C5>50),"Pass","Fail")`

Formula Breakdown:

• IF the value of cell C5 is greater than 50, the AND function will return TRUE otherwise FALSE.
• If the securing mark by John Wilkins is greater than 50 then the IF function will return Pass otherwise it returns Fail.
• Press Enter to get the output of the nested AND function, which should be Pass.

• Autofill this formula to the rest of the cells in column D.

Read More: How to Use IF with AND Function in Excel

### Example 2 – Using IF Function Nested in AND Function in Excel

• Select cell D5.
• In that cell, insert the following formula:
`=AND(IF(C5>50,"True","False"))`

Formula Breakdown:

• If John Wilkinsâ€™ securing mark is greater than 50, the IF function will return True; otherwise, it returns False.
• The AND function will return TRUE if the IF functionâ€™s output is True; otherwise, it returns FALSE.
• Press Enter to get the output of the nested AND function, which should be TRUE.

• Autofill this formula to the rest of the cells in column D.

### Example 3 – Nested Multiple IF and AND Functions in Excel

• Insert the following formula in cell D5:
`=IF(AND(C5>=80),"Excellent",IF(AND(C5>=40),"Average","Poor"))`

Formula Breakdown:

• The AND function checks whether the value in cell C5 meets the conditions inside the parentheses.
• If the value is greater than or equal to 80, it shows Excellent.
• If the value is greater than or equal to 40, it shows Average.
• Otherwise, it shows Poor.
• Press Enter to get the output of the nested multiple IF and AND functions, which should be Pass.

• Autofill this formula to the rest of the cells in column D.

### Example 4 – Nested IF and AND Functions with OR Function

Suppose you need to allocate students for a thesis or project program based on specific conditions:

1. Condition 1: The student must have a CGPA greater than 2.50 (this condition must be fulfilled).
2. Condition 2: The student must earn a total of credits greater than or equal to 110.
3. Condition 3: The student must complete at least 1 elective course.

Hereâ€™s how the eligibility works:

• A student must fulfill the first condition (CGPA > 2.50).
• After meeting the first condition, either the second or third condition must also be fulfilled for the student to be eligible for the thesis/project.

To determine eligibility, use the following formula in cell F5 and copy it to the rest of column F where you want to find the result:

`=IF(AND(C5>=3.2,OR(D5>=110,E5>=1)), "Yes","No")`

Formula Breakdown:

• The ANDÂ function checks if all conditions are met. If so, it returns TRUE; otherwise, it returns FALSE.
• The ORÂ function checks if any condition is met. If any condition is TRUE, it returns TRUE; otherwise, it returns FALSE.
• If both the CGPA condition (C5 >= 3.2) and either the credit condition (D5 >= 110) or elective course condition (E5 >= 1) are satisfied, the entire formula returns Yes; otherwise, it returns No.

By doing this you will get the desired result that you were looking for. The result is shown below.

## Things to Remember

• The #N/A!Â error occurs when the formula or a function fails to find the referenced data.
• The #DIV/0!Â error occurs when a value is divided by zero or the cell reference is blank.

## Related Articles

<< Go Back to Excel AND Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF