ASSIGNMENT – 45

ANSWER KEY – IF, AND & OR

Data Range

  • Experience → D2:D16

  • Sales → E2:E16

  • Attendance → F2:F16

  • Training → G2:G16

  • Performance → H2:H16

Q1. Check Sales Target

Condition: Sales ≥ ₹50,000

Formula

=IF(E2>=50000,"Target Achieved","Target Not Achieved")

Result

Employee Sales Result
Aarav ₹45,000 Target Not Achieved
Meera ₹38,000 Target Not Achieved
Rahul ₹52,000 Target Achieved
Pooja ₹60,000 Target Achieved
Karan ₹30,000 Target Not Achieved
Simran ₹48,000 Target Not Achieved
Mohit ₹72,000 Target Achieved
Neha ₹42,000 Target Not Achieved
Arjun ₹28,000 Target Not Achieved
Priya ₹55,000 Target Achieved
Rohan ₹65,000 Target Achieved
Ananya ₹47,000 Target Not Achieved
Vikas ₹40,000 Target Not Achieved
Riya ₹51,000 Target Achieved
Nitin ₹35,000 Target Not Achieved

Answer:

6 Employees achieved the sales target.

Q2. Check Attendance

Condition: Attendance ≥ 90%

Formula

=IF(F2>=90,"Good Attendance","Low Attendance")

Answer:

9 Employees have good attendance.

They are:

Aarav, Rahul, Pooja, Simran, Mohit, Neha, Rohan, Ananya and Riya.

Q3. Training Status

Formula

=IF(G2="Completed","Eligible","Not Eligible")

Answer:

11 Employees → Eligible

4 Employees → Not Eligible

Employees with pending training:

  • Karan

  • Arjun

  • Vikas

  • Nitin

Q4. Promotion Eligibility

Conditions:

  • Experience ≥ 5 years

  • Sales ≥ ₹50,000

Formula

=IF(AND(D2>=5,E2>=50000),"Eligible for Promotion","Not Eligible")

Eligible Employees

Employee Experience Sales
Rahul 7 ₹52,000
Mohit 8 ₹72,000
Rohan 9 ₹65,000

Answer:

3 Employees

Q5. Performance Bonus Eligibility

Conditions:

  • Sales ≥ ₹50,000

  • Attendance ≥ 90%

Formula

=IF(AND(E2>=50000,F2>=90),"Bonus Eligible","No Bonus")

Bonus Eligible Employees

Employee Sales Attendance
Rahul ₹52,000 95%
Pooja ₹60,000 96%
Mohit ₹72,000 94%
Rohan ₹65,000 97%
Riya ₹51,000 94%

Answer:

5 Employees

Q6. Special Recognition

Conditions:

  • Performance = Excellent

  • Training = Completed

Formula

=IF(AND(H2="Excellent",G2="Completed"),"Recognized","Not Recognized")

Recognized Employees

  • Rahul

  • Pooja

  • Mohit

  • Rohan

  • Ananya

  • Riya

Answer:

6 Employees

Q7. Training Requirement

Conditions:

  • Attendance < 80%

  • OR Training = Pending

Formula

=IF(OR(F2<80,G2="Pending"),"Training Required","No Training Required")

Training Required

Employee Attendance Training
Karan 75% Pending
Arjun 68% Pending
Vikas 82% Pending
Nitin 80% Pending

Answer:

4 Employees

Q8. High Performer

Conditions:

  • Sales ≥ ₹60,000

  • OR Performance = Excellent

Formula

=IF(OR(E2>=60000,H2="Excellent"),"High Performer","Regular Performer")

High Performers

  • Rahul

  • Pooja

  • Mohit

  • Rohan

  • Ananya

  • Riya

Answer:

6 Employees

Q9. Promotion + Bonus Check

Conditions:

  • Experience ≥ 5

  • Sales ≥ ₹50,000

  • Attendance ≥ 90%

Formula

=IF(AND(D2>=5,E2>=50000,F2>=90),"Promotion & Bonus","Not Eligible")

Eligible Employees

Employee Experience Sales Attendance
Rahul 7 ₹52,000 95%
Mohit 8 ₹72,000 94%
Rohan 9 ₹65,000 97%

Answer:

3 Employees

Q10. Final Employee Category

Rules:

Star Performer

Sales ≥ ₹60,000 AND Attendance ≥ 90%

Good Performer

Sales ≥ ₹50,000 OR Performance = Excellent

Needs Improvement

Otherwise

Formula

=IF(AND(E2>=60000,F2>=90),"Star Performer",IF(OR(E2>=50000,H2="Excellent"),"Good Performer","Needs Improvement"))

Results

Employee Final Category
Aarav Needs Improvement
Meera Needs Improvement
Rahul Good Performer
Pooja Star Performer
Karan Needs Improvement
Simran Needs Improvement
Mohit Star Performer
Neha Needs Improvement
Arjun Needs Improvement
Priya Good Performer
Rohan Star Performer
Ananya Good Performer
Vikas Needs Improvement
Riya Good Performer
Nitin Needs Improvement

Summary

  • Star Performer → 3

  • Good Performer → 4

  • Needs Improvement → 8

BONUS ANSWERS ⭐

Q11. Sales ≥ ₹50,000 AND Training = Completed

Formula

=COUNTIFS(E2:E16,">=50000",G2:G16,"Completed")

Employees

Rahul, Pooja, Mohit, Priya, Rohan and Riya

Answer:

6 Employees

Q12. Attendance ≥ 90% OR Sales ≥ ₹60,000

Since all employees with Sales ≥ ₹60,000 already have Attendance ≥ 90%, the result remains:

Answer:

9 Employees

Matching Employees:

Aarav, Rahul, Pooja, Simran, Mohit, Neha, Rohan, Ananya and Riya.

Q13. Sales-Based Grade

Rules:

  • Sales ≥ ₹60,000 → A Grade

  • Sales ≥ ₹40,000 → B Grade

  • Otherwise → C Grade

Formula

=IF(E2>=60000,"A Grade",IF(E2>=40000,"B Grade","C Grade"))

Results

Employee Sales Grade
Aarav ₹45,000 B Grade
Meera ₹38,000 C Grade
Rahul ₹52,000 B Grade
Pooja ₹60,000 A Grade
Karan ₹30,000 C Grade
Simran ₹48,000 B Grade
Mohit ₹72,000 A Grade
Neha ₹42,000 B Grade
Arjun ₹28,000 C Grade
Priya ₹55,000 B Grade
Rohan ₹65,000 A Grade
Ananya ₹47,000 B Grade
Vikas ₹40,000 B Grade
Riya ₹51,000 B Grade
Nitin ₹35,000 C Grade

Summary

A Grade → 3

B Grade → 8

C Grade → 4

Q14. Complete Eligibility Check

Conditions:

  • Experience ≥ 5

  • Attendance ≥ 90

  • Training = Completed

Formula

=IF(AND(D2>=5,F2>=90,G2="Completed"),"Eligible","Not Eligible")

Eligible Employees

  • Rahul

  • Simran

  • Mohit

  • Rohan

  • Ananya

Answer:

5 Employees

Q15. FINAL EMPLOYEE STATUS ⭐

Rules:

Star Performer

Sales ≥ ₹60,000 AND Attendance ≥ 90%

Excellent Performer

Performance = Excellent OR Sales ≥ ₹50,000

Needs Improvement

Otherwise

Formula

=IF(AND(E2>=60000,F2>=90),"Star Performer",IF(OR(H2="Excellent",E2>=50000),"Excellent Performer","Needs Improvement"))

Final Results

Employee Status
Aarav Needs Improvement
Meera Needs Improvement
Rahul Excellent Performer
Pooja Star Performer
Karan Needs Improvement
Simran Needs Improvement
Mohit Star Performer
Neha Needs Improvement
Arjun Needs Improvement
Priya Excellent Performer
Rohan Star Performer
Ananya Excellent Performer
Vikas Needs Improvement
Riya Excellent Performer
Nitin Needs Improvement

Final Summary

Category Number
Star Performer 3
Excellent Performer 4
Needs Improvement 8
Total 15

FINAL ANSWER SUMMARY

Question Answer
Q1 6 Target Achieved
Q2 9 Good Attendance
Q3 11 Eligible
Q4 3 Promotion Eligible
Q5 5 Bonus Eligible
Q6 6 Recognized
Q7 4 Training Required
Q8 6 High Performers
Q9 3 Promotion & Bonus Eligible
Q10 3 Star, 4 Good, 8 Needs Improvement
Q11 6 Employees
Q12 9 Employees
Q13 3 A, 8 B, 4 C
Q14 5 Eligible
Q15 3 Star, 4 Excellent, 8 Needs Improvement

QUICK REVISION

IF

Makes a decision:

=IF(condition,"Yes","No")

AND

All conditions must be TRUE.

=AND(A2>=10,B2>=20)

OR

At least one condition must be TRUE.

=OR(A2>=10,B2>=20)

IF + AND

=IF(AND(A2>=10,B2>=20),"Eligible","Not Eligible")

IF + OR

=IF(OR(A2>=10,B2>=20),"Eligible","Not Eligible")

Nested IF

Used when there are multiple levels of decisions:

=IF(A2>=60,"A",IF(A2>=40,"B","C"))

Key Concept: AND makes the condition stricter because every condition must be TRUE, while OR is more flexible because any one condition can be TRUE.

PreviousNext