All articles with 'homework' Tag

Rounding time to nearest minute or quarter hour etc. [formulas]

Published on Jun 26, 2017 in Excel Howtos
Rounding time to nearest minute or quarter hour etc. [formulas]

The other day, I was building a spreadsheet to calculate FTE (full time equivalent) for staff based on hours worked on various days in a fortnight. While building the spreadsheet, I came across an interesting problem. Rounding Time to nearest minute.  We can’t use ROUND() or MROUND() to round time as these formulas aren’t designed to work with time values. Although time values are technically decimal, rounding time to nearest minute (or quarter hour etc.) can be tricky when usual round formulas. Let me share a few formulas to round time to nearest point.

Let’s say you have a time value (either user input or calculated) in cell A1.

Use below formulas to round time in A1.

Continue »

There are 5 hidden cells in this workbook – Find them all [Excel Easter Eggs]

Published on Apr 14, 2017 in Excel Challenges
There are 5 hidden cells in this workbook – Find them all [Excel Easter Eggs]

It is Easter time, and that means time for another fun Easter Egg hunt in the spreadsheet. For the last 8 years (since 2009), I have been running Easter Egg hunt at Chandoo.org. This year too, I have prepared an exciting egg extraction enigma for you. Check it out.

Can you find all the 5 hidden cells in this workbook?

First step. Download this workbook. There are five worksheets, each containing one egg. You need to find the hidden cell in each worksheet.

Continue »

How many employees are on leave during Easter holidays [Homework]

Published on Apr 7, 2017 in Excel Challenges
How many employees are on leave during Easter holidays [Homework]

Easter is around the corner. After what seemed like weeks of lousy weather, finally the sun shone today. I capitalized on the day by skipping work, walking kids to school, taking Jo out for some shopping, enjoying a leisurely walk / cycling with Nishanth in the park and almost forgetting about the blog. But it is dark now and before tucking the kids in, let me post a short but interesting home work problem.

Let’s say you are HR manager at Egg Co. and you are looking at the vacation plans of your team.

Easter is your busiest time and it would be a bummer if a majority of your staff are on leave during the Easter season (14th of April to 28th of April, 2017). So you want to know how many people are on leave. A snapshot of your data (table name: lvs) is shown above.

Click here to download the sample file.

You want to answer below three questions:

  1. How many employees are on leave during Easter holidays (14th of April to 28th of April)?
  2. How many employees are on approved vacation during Easter holidays?
  3. How many employees in “Team ninja” are on approved leave during Easter holidays? Assume team employee numbers are in named range ninja
Continue »

Lookup most frequent item [Homework]

Published on Feb 17, 2017 in Excel Challenges
Lookup most frequent item [Homework]

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
Check for two out of three conditions (Homework)

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 »

Can you solve this blood pressure problem? [IF Formula Homework]

Published on Nov 4, 2016 in Formula Challenges, Learn Excel
Can you solve this blood pressure problem? [IF Formula Homework]

Over on Facebook, Kristin asks, Help, my blood pressure is going thru the roof. I can’t seem to solve this blood pressure problem. 

Let’s simplify Kristin’s problem.

You have some data in the format shown above.

And you want to find out the BP category for each reading, using some rules. Read on to solve the problem.

Continue »

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.

extract-10-digits

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 Chandoo.org Podcast Sessions
CP053: Excel Data Validation for Dummies

In the 53rd session of Chandoo.org 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 »