All articles in 'Excel Challenges' Category
Few days ago, I asked you “How many hours did Billy work?” There were more than 100 responses with lots of innovative solutions.
So today, let’s examine various ways to calculate total working hours given start & end times of tasks. Please watch below video.
Calculating Bill’s total working hours (video)Continue »
Here is a simple but tricky problem. Imagine you are the HR manager of a teeny-tiny manufacturing company. As your company is small, you just have one employee in the shop floor. He is Mr. Billy. As this is a one person production facility, Billy has the flexibility to choose his working hours. At the […]Continue »
I am in Sydney and yesterday we had a meetup of Sydney Excel users. Around 15 people turned up for the event and we talked about various Excel related topics over few drinks. One of the questions that came up was,
I learn and use Excel in better ways. But my boss doesn’t how to open the workbook and use simple stuff like filters, slicers, sorting and selecting. So I end up sending screenshots or PDFs instead of powerful Excel files. What to do?
Although we discussed various possible solutions to the problem, I thought it would be a good topic for us discuss here.
So how would you train your boss to use Excel?
Please share your thoughts, experiments, experiences and suggestions in the comments. Let’s make our bosses awesome.Continue »
Time for another homework problem. Assuming you have 2 ranges of values like below, how do you check if both of them have same set of values?Continue »
Here is a fun problem to think about.
Let’s say you are looking at some data like above.
And you want to find out what is the longest streak of wins in the list.
How do you calculate it?
bonus question: What formula calculates when the longest streak began?Continue »
Imagine you run a cute little pastry in Rome (Italy). To boost the sales you have a 2 person sales team. Caterina & Antonio. Caterina is the manager & Antonio, her assistant. Apart from basic salary, they will also receive sales commission. This comes from a portion of net profit allocated to “incentive pool”. The […]Continue »
Occasionally we deal with data that is so uncooperative that we might as well give up and go back to calculators & ledger books.
Recently I found myself in such a situation and learned something new.
Introducing … data that won’t play nice
Drum roll please. Here is a data-set that I got from somewhere.
The problem – build a lookup formula
And the problem. Oh, simple. Write a lookup formula to find how many customer walk-ins we have on any given day.
But how?Continue »
Its homework time again. This time, lets tackle an interesting & everyday problem. Lets introduce our protagonist of the story – Jack. Jack likes long road trips, smell of freshly brewed Colombian coffee, clicky-clack sound of his computer keyboard. He hates toll plazas (they slow him down) & Potassium permanganate. And oh yes, Jack is […]Continue »
Finally the wait is over. Here are the winners of our 2014 dashboard contest. Check them out.Continue »
It is Easter time again. Every year, we at Chandoo.org celebrate Easter by hunting for eggs (not in the wild, just in the Excel workbooks). This year too I have hidden 30 Excel tips in a workbook. You have to solve 3 puzzles to find them. So what are you waiting for. Go ahead and hunt for them eggs.
I am sure you will enjoy themContinue »
Visualize state to state migration data and you could win an iPad or Galaxy Tab [Datavis Contest 2014]
Time for another dashboard contest. Sit straight, set aside your appointments and get number-crunching.
Analyze state to state migration data between 2012 & 2010 and present your results in a dashboard (or charts) and you could walk away with glory, fame & and an iPad.
If all of this sounds tempting, read on.
- Download dataset for the contest [mirror]
- Analyze the data and create a dashboard or set of charts (see rules below)
- Submit your workbook by 20th of April (Sunday), 2014.
- If readers at chandoo.org and our panel of judges love your work, you get one of the cool prizes.
Today lets rescue John Doe from John_doe@email.com.
Extract first & last name from email address
Given an email address in the format
You need to extract first name & last name using formulas.Continue »
Blank cells are an invisible pain in the analysis. Dealing with them is frustrating, time consuming and often very complex. At chandoo.org, we are not big fans of blank cells. That is why we wrote:
- How to delete blank cells & rows?
- Dealing with blanks – case study
- Quickly filling blank cells in a table
- Extracting non-blank data from a list
Today, lets talk about one more scenario. Lets say you want to find out the first non-blank item in a list. How would you do it?Continue »
Its Friday, that means time for another Excel challenge for you.
Calculate vacation days in a period:
Your mission, if you choose to accept it,
Step 1: Download the hom work problem file.
Step 2: Calculate number of vacations taken in a period. Specifically,
1) How many vacations are taken between start & end dates, assuming complete vacation should be inside the start & end date period?
2) How many vacations are taken such that at least one day of vacation is between start & end dates?
3) How many people took vacations? (if same person took multiple vacations, then count it as 1)
Are you ready for an Excel challenge?
Today, your job is very simple. Just find a pattern in a text and return corresponding value.
In a range we have some resource types & their billing rates.
In another range, we have some descriptions. Each description contains a resource type somewhere inside it. We need to retrieve billing rate for each description by looking up which resource type is mentioned in it.
See above diagram.Continue »