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.
Read More: How to Return TRUE or FALSE Using Excel AND Function
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.
Read More: How to Use Conditional Formatting with AND Function in Excel
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:
- Condition 1: The student must have a CGPA greater than 2.50 (this condition must be fulfilled).
- Condition 2: The student must earn a total of credits greater than or equal to 110.
- 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.
Read More: How to Use IFS and AND Functions Together in Excel
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.
Download Practice Workbook
You can download the practice workbook from here:
Related Articles
<< Go Back to Excel AND Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
