# 91|EXCEL – Lending a Helping Hand

BYU Student Author: @Marco
Reviewers: @Spencer, @Christian, @Erick_Sizilio
Estimated Time to Solve: 15 Minutes

We provide the solution to this challenge using:

Overview
You’ve been working at a big tech company called Pear Inc. for a couple of years as an accountant. You enjoy working there and helping your co-workers. After lunch, you notice that your new co-worker in the HR department, Jane, is having some trouble. You ask her if there’s anything you can help her with. She responds by asking if you can help her analyze some employee data on excel. You happily accept and begin conducting your analysis.

Instructions

1. She tells you there are duplicates and would like to delete them.
2. Make a new sheet and call it “Info Needed”, this is where you’ll put all info from the next instructions.
3. Separate employee names to only show first names and name the column “First Name”.
4. Calculate the age of every employee and name the column “Age”.
5. Make a column named “Birth Month” and show the birth month of every employee (ex: December, August).
6. Calculate the # of years the employee has been employed at the company (no decimals) and name the column “Years Employed”.
7. Bonuses are being given out to the employees who have worked 10 or more years.
• Highlight the cells that are 10 or more years in the “Years Employed” column.
• Make a new column named “# of Bonuses” with the number of employees that have worked 10 or more years.
8. Jane would like to start preparing birthday cards for people who have birthdays in December, make a column named “# of December Birthdays” and calculate the number of birthdays in December.
9. Make a column named “Average Age” and calculate the average age of employees.

Data Files

Solution

Sona.xlsx (501.9 KB)

TechHub91.xlsx (430.9 KB)
Great practice!

0210_Porter_Challenge91.xlsx (431.0 KB)

Challenge91_Pear_Inc._Dataset.xlsx (611.8 KB)

Challenge91_Pear_Inc._Dataset.xlsx (512.7 KB)

Challenge91_Pear_Inc._Dataset.xlsx (446.1 KB)

Challenge91_Pear_Inc._Dataset.xlsx (421.9 KB)

Challenge91_Pear_Inc._Dataset.xlsx (423.3 KB)

Challenge91_Pear_Inc._Dataset.xlsx (407.8 KB)

Challenge91_Pear_Inc._Dataset.xlsx (765.7 KB)

This took me longer than I expected, but it was a great way to practice pulling this information efficiently. I did the age and years employed a little differently by using the “=YEAR()” formula, but I got the same results. Fun to see how the results have changed as time has passed. Thanks!

Challenge91_Pear_Inc._Dataset.xlsx (446.6 KB)

Challenge91_Pear_Inc._Dataset.xlsx (549.6 KB)

Challenge91_Pear_Inc._Dataset.xlsx (709.3 KB)

I appreciated using chat gpt to find a variety of ways to complete this challenge. I learned a handful of different functions that can be used with the dates, and I also got some good practice with data parsing. Here’s my solution!
Challenge91_Pear_Inc._Dataset.xlsx (422.1 KB)