All articles in 'Excel Challenges' Category
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 »
Its contest time again! Put on your creative hats & bring your Excel skills to the game.
Analyze more than 1900 survey responses & present your results in a stunning fashion, and you could walk away with an XBOX 360 + Kinect Sports Bundle (valued at $299).
Sounds interesting? Read on.Continue »
So who is up for an Excel challenge?
Shelly, who is an HR Manager sent this distress call last week,
“I have a group of employees- lets say 100 employees. Each employee has a performance rating attached to them. I want to divide the group by 5%, 15%, 65%, 10%, 5% based on their performance rating.”
And that is our challenge today. Read on & help Shelly.Continue »
If I were to hire an data analyst, I would simply ask them to write a complex IF formula in Excel. If they can write it, the interview progresses, else, they are out. In other words,
=IF(person_can_write_big_fat_IF_formula=TRUE, proceed_with_interview, say_thanks_and_call_next_person)
If you are able to write IF formulas for any situation, then you are bound to be awesome in Excel.
So, to test how well you know your IFs & Boolean functions, let me give you a small challenge.Continue »
While I was away, Hui did a splendid job of starting a new series called Formula Forensics. The idea is to break down formulas for difficult real-world problems so that we can understand them better. In that spirit, I am giving you an interesting and tough formula homework. Situation: Imagine you work for Large Fries […]Continue »