All articles with 'homework' Tag

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.

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.

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

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?

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.

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.

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 n 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.

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.

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.

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.

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.

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 […]

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

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

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.

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.