All articles with 'challenge' Tag

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 »

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 »

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 »

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 »

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 »

Highlight Employees by Performance Rating – Conditional Formatting Challenge

Published on May 18, 2012 in Excel Challenges, Learn Excel
Highlight Employees by Performance Rating – Conditional Formatting Challenge

So who is up for an Excel challenge?

Shelly, who is an HR Manager sent this distress call last week,

“I have a group of employees- lets say 100 employees. Each employee has a performance rating attached to them. I want to divide the group by 5%, 15%, 65%, 10%, 5% based on their performance rating.

And that is our challenge today. Read on & help Shelly.

Continue »

A Challenge from Hui

Published on Oct 3, 2011 in Excel Challenges, Posts by Hui

Hi All. I am about to head off on holidays for a couple of weeks during which time I will only have limited internet access and won’t be helping out here at Chandoo.org or at the ExcelHero Academy whilst away. I thought I may leave a few words and a Challenge for everyone. History In […]

Continue »

Get busy this weekend, with OR XOR AND [Excel Homework]

Published on Jul 29, 2011 in Excel Challenges, Excel Howtos
Get busy this weekend, with OR XOR AND [Excel Homework]

Some of you have heard my neighbor’s dog bark in a video or two that I recorded. While I was busy explaining how to do something awesome in Excel, this dog would decide to bark, adding her own two cents to the lesson. Quite a few of my VBA class students have grown used to it. So much that they complain when a lesson doesn’t have a couple of woofs. But I digress.

So coming back, one of the dogs (probably stray) has decided that she should bring her infant puppies and hide them under our terrace stair case. So, now we have 2 cute little puppies barking day long (and very late in to the night) just outside my office window. We have tried hard to get rid of them, but they somehow sneak back in and start barking or crying. So, I will be busy this weekend trying to move them out.

But that doesn’t mean, you have to live Excel-less for a few days. So I have a homework.

OR XOR AND, Get busy this weekend!

Don’t worry. I am not speaking elvish or something. OR, XOR & AND stand for bit-wise operations. This week, your task is to write formulas in Excel that would get the bit-wise results for AND, OR & XOR.

Continue »

The Zoho Visualization Challenge – Deadline Extended to 5th Dec

Published on Nov 27, 2009 in blogging

Just a quick update, I am extending the visualization challenge #2‘s dead line to 5th Dec. This will give time to our members in US as they are busy celebrating thanksgiving day. Go here for details about the contest, if you win, you will get a cool iPod Touch, sponsored by Zoho Reports. PS: Have […]

Continue »

Visualization Challenge – Budget vs. Actual Performance

Published on Mar 18, 2009 in Charts and Graphs, Featured, Learn Excel

Here is your chance to win a copy of The Visual Display of Quantitative Information by Edward Tufte, all you need to do is visualize budget vs. actual performance of the given data. Go!

Continue »