ASSIGNMENT – 45

USE OF IF, AND & OR – EMPLOYEE ELIGIBILITY & PERFORMANCE ANALYSIS

Objective

In this assignment, you will learn how to use:

  • IF

  • AND

  • OR

  • Multiple Conditions

  • Nested Conditions

  • Logical Decision Making

EMPLOYEE PERFORMANCE DATA

Emp ID Employee Name Department Experience Sales Attendance % Training Performance
101 Aarav Sales 3 45000 92 Completed Good
102 Meera HR 5 38000 88 Completed Good
103 Rahul Sales 7 52000 95 Completed Excellent
104 Pooja IT 4 60000 96 Completed Excellent
105 Karan HR 2 30000 75 Pending Average
106 Simran Sales 6 48000 90 Completed Good
107 Mohit IT 8 72000 94 Completed Excellent
108 Neha HR 4 42000 91 Completed Good
109 Arjun Sales 1 28000 68 Pending Average
110 Priya IT 5 55000 89 Completed Good
111 Rohan Sales 9 65000 97 Completed Excellent
112 Ananya HR 6 47000 93 Completed Excellent
113 Vikas IT 3 40000 82 Pending Average
114 Riya Sales 4 51000 94 Completed Excellent
115 Nitin HR 7 35000 80 Pending Average

QUESTIONS

Q1. Check Sales Target

If Sales are ₹50,000 or more, display:

“Target Achieved”

Otherwise:

“Target Not Achieved”

Function: IF

Q2. Check Attendance

If Attendance is 90% or more, display:

“Good Attendance”

Otherwise:

“Low Attendance”

Function: IF

Q3. Training Status

If Training is Completed, display:

“Eligible”

Otherwise:

“Not Eligible”

Function: IF

Q4. Promotion Eligibility

An employee is eligible for promotion if:

  • Experience ≥ 5 years

  • AND

  • Sales ≥ ₹50,000

Display:

“Eligible for Promotion”

Otherwise:

“Not Eligible”

Functions: IF + AND

Q5. Performance Bonus Eligibility

An employee is eligible for a bonus if:

  • Sales ≥ ₹50,000

  • AND Attendance ≥ 90%

Display:

“Bonus Eligible”

Otherwise:

“No Bonus”

Functions: IF + AND

Q6. Special Recognition

An employee receives special recognition if:

  • Performance = Excellent

  • AND Training = Completed

Display:

“Recognized”

Otherwise:

“Not Recognized”

Functions: IF + AND

Q7. Training Requirement

If an employee has:

  • Attendance < 80%

  • OR Training = Pending

Display:

“Training Required”

Otherwise:

“No Training Required”

Functions: IF + OR

Q8. High Performer

An employee is a High Performer if:

  • Sales ≥ ₹60,000

  • OR Performance = Excellent

Display:

“High Performer”

Otherwise:

“Regular Performer”

Functions: IF + OR

Q9. Promotion + Bonus Check

An employee is eligible for “Promotion & Bonus” if:

  • Experience ≥ 5 years

  • AND Sales ≥ ₹50,000

  • AND Attendance ≥ 90%

Otherwise display:

“Not Eligible”

Functions: IF + AND

Q10. Final Employee Category

Create a final category using the following rules:

If:

Sales ≥ ₹60,000 AND Attendance ≥ 90%

→ “Star Performer”

Otherwise, if:

Sales ≥ ₹50,000 OR Performance = Excellent

→ “Good Performer”

Otherwise:

→ “Needs Improvement”

Functions: IF + AND + OR

BONUS CHALLENGE ⭐

Q11.

Find employees who have Sales ≥ ₹50,000 AND Training = Completed.

Q12.

Find employees who have Attendance ≥ 90% OR Sales ≥ ₹60,000.

Q13.

Create a formula that displays:

  • Sales ≥ ₹60,000 → A Grade

  • Sales ≥ ₹40,000 → B Grade

  • Otherwise → C Grade

Use Nested IF.

Q14.

Create an eligibility formula:

Experience ≥ 5 AND Attendance ≥ 90 AND Training = Completed

→ “Eligible”

Otherwise → “Not Eligible”

Q15. Final Challenge ⭐

Create one Employee Status column using all three logical functions:

  • IF

  • AND

  • OR

Rules:

Star Performer
→ Sales ≥ ₹60,000 AND Attendance ≥ 90%

Excellent Performer
→ Performance = Excellent OR Sales ≥ ₹50,000

Needs Improvement
→ Otherwise

FORMULA PRACTICE

IF

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

AND

Both conditions must be TRUE.

=AND(D2>=5,E2>=50000)

OR

At least one condition must be TRUE.

=OR(E2>=60000,H2="Excellent")

IF + AND

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

IF + OR

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

IF + AND + OR

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

IMPORTANT DIFFERENCE

Function Meaning
IF Makes a decision
AND All conditions must be TRUE
OR At least one condition must be TRUE
IF + AND Decision based on multiple conditions that must all be satisfied
IF + OR Decision when any one condition is satisfied
Nested IF Multiple levels of decisions

STUDENT TASK

Complete all 15 questions using Excel formulas.

Do not manually type the results.

Use formulas so that if the employee data changes, the result automatically updates.

PreviousNext