All articles in 'Excel Challenges' Category
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 »
Its Home work time folks. Sharpen your Excel pencils and get cracking.
Find out if 2 dates are in same month
Lets say you have 2 dates in A1, A2.
Q1. What formula tells us if both of them are in same month?
Both dates must be in same month & year!
Go ahead and post your answer in comments.Continue »
Here is a formula challenge for you. Lets say we have 2 lists of values in A1:A10 & B1:B10 Now, how do you find the number of common values in both lists? We just want the count, not list of common values it self. Go ahead and figure out the formula and post your answers […]Continue »
Finally the wait is over. Eager to know who won our Excel Salary Survey Dashboard contest? Read on.Continue »
So who is up for a challenge? Can you use only formulas and extract dates buried inside text?
- Download this file.
- In column C, write a formula such that you can extract the date in column B
- If you succeed, post your solution here as a comment.
- If you fail, drink some coffee, start afresh.
Watching the Olympic athletes run & jump all I could think of is,
- What should I eat to jump & sprint like that?
- How come I never heard about steeple chase?
- Should we really have 3 bullet points in all lists?
But I digress. Coming back, when watching one of those hurdles events, I got an idea as sharp as Chinese table tennis team.
Why not create a hurdles game in Excel to measure how good you are with keyboard?
So ladies & gentleman, let me present you our very own Olympics hurdle run.Continue »