All articles with 'excel formulas' 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 »

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 »

How to highlight overdue items [video]

Published on Aug 3, 2015 in Learn Excel
How to highlight overdue items [video]

We, adults can’t escape three things:

  1. Deadlines
  2. Demanding bosses (replace with customers or nagging spouses or naughty kids)
  3. Taxes

While I can’t help you with demanding bosses or taxes, when it comes to deadlines, I have the right tool for you.

A tracker that highlights all overdue items so that you know where to focus your attention.

Let’s learn how to use awesome powers of Excel to find-out which items are due. You can apply these concepts to nail down over due invoices, pending project tasks or scheduling workforce.

Continue »

How to find out if a text contains question? [Excel formulas]

Published on Jul 17, 2015 in Excel Howtos, Learn Excel
How to find out if a text contains question? [Excel formulas]

On Wednesday (15th July), I ran my first ever webinar, on a topic called, “How to be a BETTER Analyst?” (here is the replay link, in case you missed it).  It was a huge success. More than 1,100 people attend the live webinar and hundreds more watched the replay. As part of the webinar, we had interactive Q&A. Viewers posted their questions and I replied to as many of them as I can.

After the webinar, I wanted to make sure I covered all the questions. So I downloaded the chat history. There were more than 700 messages in it. And I am not in the mood to read line by line to find-out the questions. A good portion of chat messages were not questions but stuff like ‘hello everyone, I am from Idaho’, ‘Wow, Chandoo has beard!”, “Enjoying a beer in Belgium while watching webinar” etc. So I wanted a quick way to flag the messages as question or not.

Continue »

Are you an analyst? Use these 25 shortcuts & tricks to boost your productivity

Published on Jul 7, 2015 in Keyboard Shortcuts, Learn Excel
Are you an analyst? Use these 25 shortcuts & tricks to boost your productivity

Analyst’s life is busy. We have to gather data, clean it up, analyze it, dig the stories buried in it, present them, convince our bosses about the truth, gather more evidence, run tests, simulations or scenarios, share more insights, grab a cup of coffee and start all over again with a different problem.

So today let me share with you 25 shortcuts, productivity hacks and tricks to help you be even more awesome.

Continue »

CP037: Error error on the wall, How do I fix you all? – Understanding & Fixing Excel Errors

Published on Jun 25, 2015 in Podcast Sessions
CP037: Error error on the wall, How do I fix you all? – Understanding & Fixing Excel Errors

In the 37th session of podcast, Let’s debug ’em #VALUEs & #N/As.

What is in this session?

In this podcast,

  • Introduction to Excel formula errors
  • The easy kind: syntax errors
  • The triky ones: # ERRORs
  • Fixing errors – using IFERROR & ISERROR
  • Error checking & debug options
  • Using Errors deliberately – charts & data validation
  • A challenge for you – produce #NULL error
  • Conclusions
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 »

Narrating the story of change using Excel charts – case study


Here are three questions you often hear from your boss:

  1. What changes are happening in our business and how do they look?
  2. Do you know how to operate this new coffee machine?
  3. Why does every list has 3 items?

Jokes aside, our urge to find change in environment predates cave drawing, slice bread and Tommy Lee Jones. So, today let’s examine a very effective chart that tells the story of change and re-create it in Excel.

Continue »