# All articles in 'Excel Challenges' Category

## Lookup most frequent item [Homework]

Published on Feb 17, 2017 in Excel Challenges

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 »

## Check for two out of three conditions (Homework)

Published on Jan 13, 2017 in Excel Challenges

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 »

## An odd lookup problem [Formulas]

Published on Dec 6, 2016 in Excel Challenges, Learn Excel

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 »

## Check if a range has all numbers from 1 to n [Homework]

Published on Sep 30, 2016 in Excel Challenges, Learn Excel

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 in your formulas.

See above examples to understand the problem.

Continue »

## Find the seals [Excel puzzle]

Published on Aug 5, 2016 in Excel Challenges

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 »

## How to visualize multiple variables over several years? [Contest]

Published on Jun 24, 2016 in Charts and Graphs, Excel Challenges

Our newest contest is inspired from a question asked by Kaushik, one of our forum members, interesting problem.

Need to quickly visualize 3 variables ( Company, years, Financials) in a single […] chart.

Create a chart to understand multiple variable data and you could win \$100 Amazon gift card. Do send your charts before 4th of July to qualify for the prizes.

Continue »

## What is the sum of values excluding items on stop list? [home work]

Published on Jun 10, 2016 in Excel Challenges, Learn Excel

Okay, this is an extension of the Neither “A” Nor “B” sum problem we discussed few days back.

Imagine you have a table named mydata with a few columns and a stop list named stop.list as shown above.

How would you calculate,

• Sum of Hours for all activities excluding those in stop list?
• Sum of all Regular hours for activities not in stop list?

Continue »

## Extract the 10 digit number [formula homework]

Published on May 6, 2016 in Excel Challenges

Okay, time for another challenge.

Imagine you have some data like this. Each cell contains 3 numbers separated by line break  – CHAR(10) and you need to extract the number that is 10 digits long.

Go ahead and solve this riddle.

Continue »

## Sumerian Voter Problem [IF formula homework]

Published on Apr 22, 2016 in Excel Challenges

Here is a simple IF formula challenge for you. Go ahead and post your answers in the comments section. Can this person vote in Sumeria? Imagine you are the chief election officer in the great country of Sumeria. You have introduced a new eligibility criteria for voters just before the grand presidential elections of 2016. In order […]

Continue »

## There are seven pandas hidden in this workbook [Easter Eggs]

Published on Mar 25, 2016 in Excel Challenges

It is Easter time again. This year, we drove to my brother’s house in Hyderabad (700 km away from my home) to spend a weekend doing absolutely nothing (we will eat copious amount of food, share family memories, laugh and laze). It is Chandoo.org tradition to share few puzzles during Easter time, a la an Excel themed virtual Easter egg hunt. This year, I have prepared an amazing challenge for you.

Continue »

## Transpose this address data [VBA homework]

Published on Jan 16, 2016 in Excel Challenges, Power Query, VBA Macros

Here is an interesting problem to keep you busy.

Transpose the address data in column A into the format indicated in C:G using either VBA, formulas or Power Query. Once done, post your answers in comments section.

Continue »

## How many Mondays between two dates? [homework]

Published on Dec 18, 2015 in Excel Challenges

Here is a quick but challenging homework problem for you.

Let’s say you have two dates – Start and End.

And you want to find out how many Mondays are there between those two dates (including the start & end dates).

What formula would give the answer?

Continue »

## Can you extract numbers from text – homework

Published on Oct 30, 2015 in Excel Challenges, Learn Excel

Here is a quick homework to keep you busy this weekend.

Can you extract number of days from below text.

```Nov15 PUTS (23 days) March15 TIKS (3 days) March1 TIKS (25 days) June11 TIKS (10 days)```

Assume the data is from cell A1.

Your solution should return the following:
```23 3 25 10```

Continue »

## Contest: Visualize KPI data

Published on Oct 23, 2015 in Charts and Graphs, Excel Challenges

Here is a quick visualization challenge for you.

There are 25 KPIs and each has a base value, current month, corresponding month values. The KPIs also have targets.

The data looks like this:

### Rules for this contest:

• Visualize this data using one Excel chart (one chart per KPI is ok too).
• You may omit any any data
• You can use interactive charts.

Continue »

## Calculating Billy’s total working hours [solution & discussion]

Published on Jun 22, 2015 in Excel Challenges

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 »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.