All articles with 'homework' Tag

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

Published on Sep 30, 2016 in Excel Challenges, Learn Excel
Check if a range has all numbers from 1 to n [Homework]

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.

So go ahead and post your formulas in the comments section. I will sneak in whenever I can to look at all your creative answers.

Continue »

Find the seals [Excel puzzle]

Published on Aug 5, 2016 in Excel Challenges
Find the seals [Excel puzzle]

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 »

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

Published on Jun 10, 2016 in Excel Challenges, Learn Excel
What is the sum of values excluding items on stop list? [home work]

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?

So go ahead and post your answers in the comments.

Continue »

How many ‘Friday the 13th’s are in this year? [Formula fun + challenge]

Published on May 13, 2016 in Excel Howtos, Learn Excel
How many ‘Friday the 13th’s are in this year? [Formula fun + challenge]

Today is Friday the 13th. If you are a raging friggatriskaidekaphobiac, I suggest you to stop reading this post. For the rest of you, I have something fun.

Given a year in cell C3, let’s find out all the months with Friday the 13th. Something like above.

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 »

CP053: Excel Data Validation for Dummies

Published on Mar 10, 2016 in Podcast Sessions
CP053: Excel Data Validation for Dummies

In the 53rd session of podcast, let’s talk about data validation.

What is in this session?

In this podcast,

  • What is data validation
  • How Excel DV compares with database & software DV?
  • Types of data validation rules
  • List & custom rules explained
  • Input & error messages
  • Alternatives to data validation
  • Enhancing data validation
  • Removing data validation rules
  • Homework problem for you
  • Resources & show notes
Continue »

Transpose this address data [VBA homework]

Published on Jan 16, 2016 in Excel Challenges, Power Query, VBA Macros
Transpose this address data [VBA homework]

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
How many Mondays between two dates? [homework]

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?

Please post your formulas / VBA functions / DAX measures in the comments section.

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:

Post your answers (formulas, VBA code or Power Query M code) in the comments.

Continue »

In-cell 5 star chart – tutorial & template

Published on Aug 15, 2015 in Charts and Graphs, Excel Howtos
In-cell 5 star chart – tutorial & template

Whenever we talk about product ratings & customer satisfaction, 5 star ratings come to our mind. Today, let’s learn how to create a simple & elegant 5 star in-cell chart in Excel. Something like above.

Read on to learn how to create the above chart.

Continue »

VLOOKUP the last value

Published on Aug 11, 2015 in Excel Howtos, Learn Excel
VLOOKUP the last value

VLOOKUP is one of the most useful Excel functions. So much so that I even wrote a book about it. But it has one serious limitation.

It looks up the first occurrence and returns corresponding data.

What if you want to find the last value?

Say, for example, you are looking at a task assignment list and want to know what is the last task assigned to employee Emp13?

We want to extract the task “Make amazing workbook”. Of course our good old VLOOKUP stops once it finds Emp13 and returns the answer as “Create intuitive workbook”.

Continue »

Calculate sum of top 10 values [formulas + homework]

Published on Aug 4, 2015 in Formula Challenges, Learn Excel
Calculate sum of top 10 values [formulas + homework]

Analyzing top n (or bottom m) items is an important part of any data analysis exercise. In this article, we are going to learn Excel formulas to help you with that.

Let’s say you are the lead analyst at a large retail chain in Ohio, USA. You are looking at the latest sales data for all the 300 stores. You want to calculate the total sales of top 10 stores.  Read on to learn the techniques.

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 »

How many hours did Billy work? [Solve this]

Published on Jun 5, 2015 in Excel Challenges

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 »