Unit 3 IF function - Part 1

Using the IF, AND, OR functions

Scenario: An HR manager is responsible for reviewing employee performance evaluations and determining their eligibility for a promotion. The promotion criteria include meeting certain performance thresholds and having a tenure of at least three years with the company. However, there are exceptions for employees who have exceptional performance reviews, regardless of their tenure.

The criteria for determining eligibility for promotion was calculated using the following logic:

  • If an employee's performance score is 4 or higher AND their tenure is at least three years, they are eligible for a promotion.

  • If an employee's performance score is 5, they are automatically eligible for a promotion regardless of their tenure.

The HR manager has the following data:

To determine the eligibility for promotion for each employee based on the provided dataset, we can use the following formula:

=IF(OR(B2=5, AND(B2>=4, C2>=3)), "Promotion Eligible", "Not Eligible")

This simplified diagram illustrating the eligibility for promotion for each employee:

In this diagram:

  • John and Alice are not eligible for promotion because their performance score does not meet the criteria.

  • Emma and Bob are eligible for promotion. Emma's exceptional performance score (5) overrides the tenure requirement, while Bob meets both the performance score and tenure criteria.

MIN and MAX functions with arrays in Microsoft Excel allows you to find the smallest an largest values respectively within a range of cells. A


You are organizing a team outing for your department and want to ensure that team members meet specific criteria to participate. The eligibility criteria are as follows:

  • Team members must have at least one year of tenure with the company.

  • Team members must have attended at least two training sessions in the past six months.

  • There are exceptions for team members who are department managers; they are automatically eligible regardless of the other criteria.

You have the following data:

  1. Column A: Team Member Name

  2. Column B: Tenure (in years)

  3. Column C: Number of Training Sessions Attended (in the past six months)

  4. Column D: Department Manager (Yes/No)

Create IF, AND, and OR functions and Dataset (using any suitable details) to determine the eligibility of each team member for the outing.

Submit your answer to actira.tt@gmail.com