All articles in 'Excel Challenges' Category
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 »
Time for some logic check.
Suppose, you have three logic values in A1:C1 (TRUE or FALSE values in each cell)
You need to find out if ONLY two of these values are TRUE.
How would you write the formula?
Got an answer? Awesome. Just post your formula in the comments. Let’s see how much variety we can get from all of our readers.Continue »
Let’s say you have some employee data in employee name, manager name format. But the data is all in one column, with odd rows containing employee names & even rows containing manager names. Something like above.
And you want to find out who is the boss for a given employee. Say, “Andrea Nichols”.
Your regular MATCH() formula for Andrea over the data range returns wrong answer as it will find first occurrence of Andrea (which in this case happens to be on even row, hence a manager record).
So how would you write the lookup formula?Continue »
Finally, spring weather showed up in Wellington this week. We cashed it as much as possible by going on treks, cycling trips, more treks and of course doing laundry.
Anyways, I don’t have time to blog. I must go out and help kids with some cycling. But I want to keep you busy this weekend. So here is a fun homework problem.
Does my range have all numbers from 1 to n?
Let’s say you have a range called range (duh!). And you want to check if range has all the numbers 1 to n (say n=5) in it, each number appearing only once (no more, no less). You can assume the named ranges range and n in your formulas.
See above examples to understand the problem.
So go ahead and post your formulas in the comments section. I will sneak in whenever I can to look at all your creative answers.Continue »
First a little back story:
Last Friday (on 29th of July), we (Jo, kids & I) went on a day walk to Red Rocks. It is a rugged coastal walk near Owhiro bay in Wellington. It was a windy & cold day. So why did we brave the elements of nature on this 10km walk? To see seals of course. And we did find a few of them. We also caught glimpses of snowy peaks in Southern Island of New Zealand.
3 Seals Excel Puzzle
Now I can’t take you on the same walk thru internet. There is no Excel function that can teleport you from your office (or home) to Owhiro bay. So I made the next best thing.
An Excel puzzle with 3 hidden seals.Continue »