All articles in 'Excel Challenges' Category

Can you calculate the sales commission? [homework]

Published on Nov 21, 2014 in Excel Challenges

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 »

Looking up when the data won’t co-operate (case study)

Published on Nov 4, 2014 in Excel Challenges, Excel Howtos
Looking up when the data won’t co-operate (case study)

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 »

What is the average speed of this road trip? [homework]

Published on Aug 8, 2014 in Excel Challenges, Learn Excel

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 »

Winners of state migration dashboard contest

Published on Jul 7, 2014 in Charts and Graphs, Excel Challenges
Winners of state migration dashboard contest

Finally the wait is over. Here are the winners of our 2014 dashboard contest. Check them out.

Continue »

There are Easter Eggs in this Excel file!!!

Published on Apr 18, 2014 in Excel Challenges
There are Easter Eggs in this Excel file!!!

Hello Friends…,

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.

Click here to download the Easter eggs file.

I am sure you will enjoy them :)

Continue »

Visualize state to state migration data and you could win an iPad or Galaxy Tab [Datavis Contest 2014]

Published on Apr 9, 2014 in Excel Challenges
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.

The contest

  1. Download dataset for the contest [mirror]
  2. Analyze the data and create a dashboard or set of charts (see rules below)
  3. Submit your workbook by 20th of April (Sunday), 2014.
  4. If readers at chandoo.org and our panel of judges love your work, you get one of the cool prizes.
Continue »

Can you extract first name & last name from email address? [Formula Challenge]

Published on Feb 27, 2014 in Excel Challenges
Can you extract first name & last name from email address? [Formula Challenge]

Today lets rescue John Doe from John_doe@email.com.

Extract first & last name from email address

Given an email address in the format

firstnameany_non-alphabet.characterslastname@email.com

You need to extract first name & last name using formulas.

Continue »

Find first non-blank item in a list with formulas

Published on Jan 15, 2014 in Excel Challenges, Excel Howtos
Find first non-blank item in a list with formulas

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:

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 »

Can you calculate vacation days in a period? [Homework]

Published on Jan 25, 2013 in Excel Challenges
Can you calculate vacation days in a period? [Homework]

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)

Continue »

Can you find that pattern? [Homework]

Published on Dec 14, 2012 in Excel Challenges
Can you find that pattern? [Homework]

Are you ready for an Excel challenge?

Today, your job is very simple. Just find a pattern in a text and return corresponding value.

Your Homework:

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 »

Write a formula to check if two dates are in same month? [homework]

Published on Nov 2, 2012 in Excel Challenges, Excel Howtos
Write a formula to check if two dates are in same month? [homework]

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 »

How many values are common in 2 lists? [homework]

Published on Sep 14, 2012 in Excel Challenges, Excel Howtos
How many values are common in 2 lists? [homework]

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 »

Excel Salary Survey Dashboard Contest Winners

Excel Salary Survey Dashboard Contest Winners

Finally the wait is over. Eager to know who won our Excel Salary Survey Dashboard contest? Read on.

Continue »

Homework: Can you extract dates from text?

Published on Aug 17, 2012 in Excel Challenges, Excel Howtos
Homework: Can you extract dates from text?

So who is up for a challenge? Can you use only formulas and extract dates buried inside text?

  1. Download this file.
  2. In column C, write a formula such that you can extract the date in column B
  3. If you succeed, post your solution here as a comment.
  4. If you fail, drink some coffee, start afresh.
Continue »

How fast can you finish this Excel Hurdles Challenge [Spreadsheet Olympics]

Published on Aug 10, 2012 in Excel Challenges, VBA Macros
How fast can you finish this Excel Hurdles Challenge [Spreadsheet Olympics]

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 »