All articles in 'Excel Challenges' Category
I have a fun Excel lookup challenge for you. You have data as shown below and want to find the last non blank value for a given account number. For example, for acct number 2015, the answer would be Freedom. How would you solve this? Refer to this workbook for 3 possible answers. Just move […]Continue »
Welcome to annual Easter Egg hunt at Chandoo.org. This year I have a feast for you. I hid 20 Easter Eggs in this workbook. Go ahead and find them. Share your solutions, clues or struggles in the comments box.
Happy hunting.Continue »
Are you ready for a fun Excel challenge? Read on then…
You are a nautical transport manifest analyst at New Donk city harbor. But you also have a secret identity. You are a spy for Global Intelligence Organization. As part of routine inspection of cargo details, you came across a list of shipping codes that look suspicious.Continue »
Howdy folks. Almost the end of August here. Let’s wrap it up with a nice little challenge, inspired from my recent consulting gig. Say you are looking at few job titles that look similar and want to match them to correct title.Continue »
Time for another Excel formula / Power Query challenge. This is based on a common business data analysis problem. Say you have two tables – calls log and office hours. Call log tells when each call is received. Office hours tell us working hours for seven days of the week. We want to know how many calls are outside office hours.Continue »
Its Easter time. At Chandoo.org, I have tradition of publishing Easter Egg hunts since 2009. This is the first time our Easter egg hunt is on Power BI. Changing times, eh?Continue »
The other day while I was in lift (elevator), it made an alarm like sound and won’t close the doors. Turns out there are one too many people in the lift for it to operate safely. As soon as a couple of people volunteered and stepped out, it started fighting gravity and took us upstairs. […]Continue »
Last Friday, I asked you to analyze “sick leave entitlement vs. usage” data and answer homework questions. We got several interesting responses to that. Today, let me share a quick video highlighting how to analyze such data with Power Query.
This is part of our Power Mondays series, where every Monday you will learn something new & useful about Power BI, Power Query and Power Pivot.Continue »
Imagine you are the HR analyst at BigLargeCompany. You are asked 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.Continue »
Psst. Hey you, yeah, you there… have time for a quick date? A date with Excel that is.
If so, take up this homework problem and post your answers in comments.
Assuming you have an input date in cell A1,
- What is the formula for finding next Monday?
- What is the formula for finding first Monday of next month?
Post your answers in commentsContinue »
Time for some soccer fun in spreadsheets. Given a team points table like above,
How would you figure out in Excel, which teams qualify for next stage. The rules for this exercise are,
- Pick the top two teams by points
- If there is a tie, use Goal Difference (GD) to break ties (more GD is good)
- If there is a tie, use Goals For (GF) to break ties
It is Easter time. This is the first Easter we are celebrating in our new house. So it is bound to have so many special memories. For last 10 years (wow, it has been a decade of tradition), I have been running Excel based egg hunts every Easter. It all started in 2009, when I was living in Sweden (where Easter is a BIG thing). I had to share the enthusiasm with someone, so I made our first Easter egg hunt on the blog.
This year, join me on an Excel themed cryptic crossword clues – Easter egg hunt. Don’t forget to share your scores and explanations in the comments.Continue »
It is Easter time, and that means time for another fun Easter Egg hunt in the spreadsheet. For the last 8 years (since 2009), I have been running Easter Egg hunt at Chandoo.org. This year too, I have prepared an exciting egg extraction enigma for you. Check it out.
Can you find all the 5 hidden cells in this workbook?Continue »
Easter is around the corner. After what seemed like weeks of lousy weather, finally the sun shone today. I capitalized on the day by skipping work, walking kids to school, taking Jo out for some shopping, enjoying a leisurely walk / cycling with Nishanth in the park and almost forgetting about the blog. But it is dark now and before tucking the kids in, let me post a short but interesting home work problem.
Let’s say you are HR manager at Egg Co. and you are looking at the vacation plans of your team.
Easter is your busiest time and it would be a bummer if a majority of your staff are on leave during the Easter season (14th of April to 28th of April, 2017). So you want to know how many people are on leave. A snapshot of your data (table name: lvs) is shown above.
You want to answer below three questions:
- How many employees are on leave during Easter holidays (14th of April to 28th of April)?
- How many employees are on approved vacation during Easter holidays?
- How many employees in “Team ninja” are on approved leave during Easter holidays? Assume team employee numbers are in named range ninja
Here is an interesting problem to keep your brain cells fight boredom on this Friday & weekend.
Let’s say you have some data like above.
And you want to know, for a given customer name (in cell G4),
- What is the most frequent quantity?
- What is the most often purchased item?
How would you write formulas to get these answers?Continue »