Excel Challenge 56 (Beginner-Friendly): Conditional Functions

Welcome to Excel Challenge 56!

Everything you need to participate in the challenge can be found in the attached workbook. To take part:

• First, watch the challenge video and read the instructions below the video.
• Review the previously published video(s) and article(s) on which the challenge is based.
• Download the Excel workbook you will use to complete the challenge tasks.
• Put yourself to the test!

In this challenge, you are a new Data Analyst intern in the HR Analytics department of a large holding company. In your very first week, your manager gives you the following task:

Tomorrow we have a meeting with all HR managers and the CEO. I need clear insights from our employee dataset. These numbers will be used in decision-making at the very top. Please make sure everything is ready by tomorrow morning — accuracy is key.

Using employee data and your Excel skills, you’ll prepare a summary of key insights that HR leadership will rely on.

This challenge is a perfect opportunity to practice conditional calculations, sharpen your formula-building skills, and see how Excel can turn raw employee records into actionable business insights.

The Excel file contains three worksheets: Challenge, Dataset, and Solution.

Challenge

This worksheet gives you an overview of Katya’s assignment and the questions to answer. It sets the stage for her mission to deliver the report for the CEO’s meeting.

Dataset

This worksheet includes detailed employee information. Each row contains:

• Name & Surname
• Gender
• Date of Birth
• Department
• Salary
• City
• Position

Use this data to perform all your analyses.

Your tasks

The real work happens in the Solution worksheet!

You’ll find summary tables already prepared. Your task is to enter the correct formulas into the answer cells so that the tables automatically calculate:

1. The number of employees with salaries above $75,317
2. Headcounts in each department (Marketing, IT, HR, Sales, Finance, Operations)
3. The average salary in the Finance department
4. The average salary by city (New York, Miami, Los Angeles, Seattle, San Francisco, Chicago, Houston)
5. The minimum and maximum salaries in each department

Hint

Need a helping hand? Here's a handy summary of the Top 10 Conditional Functions in Excel.

We can’t wait to see how you apply your Excel skills to help deliver this critical HR analysis and prepare for the CEO’s meeting.

Good luck, and happy calculating!

Solution

Did you get the answers? Here is my solution.

For you more advanced Excel learners, were there any alternative ways to solve this challenge? We love to see all solutions. Share yours with us in our learner community on Slack!

We hope you enjoyed taking part in this challenge. Stay tuned to the GoSkills Excel Resource hub for more Excel challenges, and check out our range of expert-led Excel courses for all skill levels to further sharpen your skills.

If you enjoyed this challenge, try the Basic and Advanced Excel course to help you learn more essential formulas, functions, and practical, real-world Excel skills.