Mastering Day 16: What, Why & How
Evaluating multiple conditions simultaneously. AND requires ALL conditions to be true. OR requires AT LEAST ONE condition to be true. Nested IF handles multi-tier brackets (Grade A, B, C, D).
Real business rules are rarely simple (e.g., 'Award commission only if Sales > 1,00,000 AND Attendance > 90%'). Multi-condition logic automates complex corporate policies.
"AND is strict (every test must pass); OR is generous (any single test passes); match every open parenthesis with a close parenthesis at the end!"
What You Will Master Today
- =AND(condition1, condition2) returns TRUE only if ALL conditions are met
- =OR(condition1, condition2) returns TRUE if AT LEAST ONE condition is met
- Combining with IF: =IF(AND(Salary>=25000, Age>=21), "Eligible", "Not Eligible")
- Nested IF statements: evaluating multi-tiered performance slabs (A, B, C, D)
Step-by-Step Practical Walkthrough
For multiple mandatory criteria, nest AND inside IF: '=IF(AND(B5>=40, C5>=40), "Pass", "Fail")'.
For either-or criteria, nest OR inside IF: '=IF(OR(D5="Cash", D5="UPI"), "Verified", "Check Bank")'.
For grade tiers, nest IFs: '=IF(B5>=80, "Grade A", IF(B5>=60, "Grade B", "Grade C"))'.
Ensure every opened parenthesis has a matching closed parenthesis at the end.
State Bank Loan Eligibility Assessment Model - Haldwani Branch
Study the completed reference worksheet below. Every column illustrates the standard formatting, formula structure, and alignment applied by professional office operators.
| Applicant Name | Monthly Salary (₹) | Credit CIBIL Score | Existing Loan EMI | Loan Approval Status |
|---|---|---|---|---|
| Rajesh Sharma | ₹45,000.00 | 760 | 5000 | Approved |
| Meena Rawat | ₹22,000.00 | 780 | 0 | Rejected (Low Income) |
| Sunil Bisht | ₹55,000.00 | 620 | 12000 | Rejected (Low CIBIL) |
| Anita Pandey | ₹62,000.00 | 790 | 8000 | Approved |
| Vikas Arya | ₹38,000.00 | 740 | 2000 | Approved |
Student Academic Grade Slabs Evaluation
- Task 1: In Column D, use '=AND(B5>=60, C5>=75)' to test if student passed with required 75% attendance.
- Task 2: In Column E, create a Nested IF for Grade: >=85 is 'Grade A', >=70 is 'Grade B', >=50 is 'Grade C', otherwise 'Grade D'.
- Task 3: Formula: '=IF(B5>=85, "Grade A", IF(B5>=70, "Grade B", IF(B5>=50, "Grade C", "Grade D")))'.
Exercise Data Template Preview: Download the .xlsx file below and execute each task independently.
| Student Name | Aggregate % | Attendance % | Both Criteria Met? | Assigned Grade |
|---|---|---|---|---|
| Aarav Joshi | 88 | 92 | ||
| Bhumika Rawat | 74 | 85 | ||
| Chetan Negi | 58 | 65 | ||
| Deepak Bisht | 91 | 72 | ||
| Ekta Pandey | 42 | 80 |
In Excel 2019, 2021, and Office 365, you can also use the cleaner '=IFS(B5>=85, "Grade A", B5>=70, "Grade B", B5>=50, "Grade C", TRUE, "Grade D")' function!
Need 1-on-1 Guidance on Day 16?
Join our hands-on classroom lab sessions at Digital Skills Academy in Haldwani. Every student receives a dedicated PC workstation, live instructor reviews, and interview prep.