How many people used their entire sick leave entitlement? [Power Query / Excel homework]
I have a big day tomorrow. We are celebrating our kids (Nishanth & Nakshatra) 9th birthday this weekend. We (Jo & I) must have been possessed by demons, for we are hosting the party at our home. 12 kids (half of each kind) will be invading our house for a couple of hours. There will be balloons to blow, cakes to bake, decorations to dangle, meals to make, children to cheer, guests to greet, dishes to wash, carpets to clean, walls to varnish, furniture to fix and Chandoo to calm.
So let’s keep this quick and simple. I want you to figure out an elegant and simple way to answer below questions.
Imagine you are the HR analyst at BigLargeCompany. You need to find out whether staff at BLC (BigLargeCompany you silly) use up their full sick leave entitlement.
You have two tables – emps & leaves as illustrated below.
Your mission is to find out answers to below questions.
- How many employees used exactly 100% of their entitled sick leave?
- How many employees did not take any sick leaves?
- Listing of all employees who used 100% of their entitlement
Use either Power Query, Excel formulas or any other technique to answer the questions.
Once you have answers, post them in comments section.
Want more problems? Check out Excel homework section for some very tricky, interesting challenges.
Talk to you soon. If you need me, I will be scraping ketch up off carpets.
Update: Solution is here
If you want to learn how to solve problems like this, check out the Entitlement vs. usage analysis – solution page.
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Lenient lookup [Advanced Formula Trick]
|Leave entitlement vs. usage analysis with Power Query »