All articles with 'homework' Tag

Check if 2 ranges have same values (set equality problem)

Published on Mar 10, 2015 in Excel Challenges
Check if 2 ranges have same values (set equality problem)

Hello folks,

Time for another homework problem. Assuming you have 2 ranges of values like below, how do you check if both of them have same set of values?

Continue »

What is the length of longest winning streak? [Excel homework]

Published on Jan 30, 2015 in Excel Challenges
What is the length of longest winning streak? [Excel homework]

Here is a fun problem to think about.

Let’s say you are looking at some data like above.

And you want to find out what is the longest streak of wins in the list.

How do you calculate it?

bonus question: What formula calculates when the longest streak began?

Continue »

Can you calculate the sales commission? [homework]

Published on Nov 21, 2014 in Excel Challenges

Imagine you run a cute little pastry in Rome (Italy). To boost the sales you have a 2 person sales team. Caterina & Antonio. Caterina is the manager & Antonio, her assistant. Apart from basic salary, they will also receive sales commission. This comes from a portion of net profit allocated to “incentive pool”. The […]

Continue »

Looking up when the data won’t co-operate (case study)

Published on Nov 4, 2014 in Excel Challenges, Excel Howtos
Looking up when the data won’t co-operate (case study)

Occasionally we deal with data that is so uncooperative that we might as well give up and go back to calculators & ledger books.

Recently I found myself in such a situation and learned something new.

Introducing … data that won’t play nice

Drum roll please. Here is a data-set that I got from somewhere.

The problem – build a lookup formula

And the problem. Oh, simple. Write a lookup formula to find how many customer walk-ins we have on any given day.

But how?

Continue »

What is the average speed of this road trip? [Solution & Discussion]

Published on Aug 18, 2014 in Excel Howtos, Formula Forensics
What is the average speed of this road trip? [Solution & Discussion]

Last week, we had an interesting homework problem – What is the average speed of this road trip?

We received more than 150 answers. But to my surprise, 57 of them are wrong. So today, lets learn how to calculate the average speed correct way.

Continue »

What is the average speed of this road trip? [homework]

Published on Aug 8, 2014 in Excel Challenges, Learn Excel

Its homework time again. This time, lets tackle an interesting & everyday problem. Lets introduce our protagonist of the story – Jack. Jack likes long road trips, smell of freshly brewed Colombian coffee, clicky-clack sound of his computer keyboard. He hates toll plazas (they slow him down) & Potassium permanganate. And oh yes, Jack is […]

Continue »

Calculating Maximum Change [solutions & discussion]

Published on Mar 26, 2014 in Excel Howtos, Formula Forensics
Calculating Maximum Change [solutions & discussion]

Last Friday, we had a fun little Excel challenge – Calculate Maximum Change. More than 170 people commented and shared their solutions to this problem.

And the best part?

The best part is the variety of solutions & thinking displayed by our community. So if you are one of those 170, puff your chest & pat yourself on the back. Go ahead, I will wait.

Today, lets take a look at some of these awesome formulas and understand how they work. Read on and watch the video you below to gain few awesomeness pounds.

Continue »

Calculate maximum change [homework]

Published on Mar 21, 2014 in Formula Challenges
Calculate maximum change [homework]

Today, lets see how good your formula skills are.

Calculate maximum change

Can you calculate what is the maximum change in product sales between 2 months for above data?

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 »

Find first non-blank item in a list with formulas

Published on Jan 15, 2014 in Excel Challenges, Excel Howtos
Find first non-blank item in a list with formulas

Blank cells are an invisible pain in the analysis. Dealing with them is frustrating, time consuming and often very complex. At chandoo.org, we are not big fans of blank cells. That is why we wrote:

Today, lets talk about one more scenario. Lets say you want to find out the first non-blank item in a list. How would you do it?

Continue »

Sort by Birthday [Quick tip]

Published on Aug 26, 2013 in Excel Howtos
Sort by Birthday [Quick tip]

Lets say you have a list of employees and their birthdays. Now you want to sort this list, based on their birthday, not age. How would you do it?

Continue »

How to get VLOOKUP + 1 value?

Published on Jun 25, 2013 in Excel Howtos

Here is a question someone asked me in a class recently.

“I know how to use VLOOKUP to find a value based on search term. But I have a slight variation to it. I need to extract value below the cell VLOOKUP finds.”

This is simpler than it sounds.

We can use INDEX + MATCH formulas to do this.

Continue »

How well do you know your LOOKUPs? – Quiz Answers & Discussion

Published on Apr 4, 2013 in Excel Howtos, Learn Excel, Quizzes
How well do you know your LOOKUPs? – Quiz Answers & Discussion

Last week, we had our very first quiz – “How well do you know your LOOKUPs?”. I hope you have enjoyed it.

Today lets understand the answers & explanations for this quiz.

Continue »

How well do you know your Lookups? [Quiz]

Published on Mar 22, 2013 in Quizzes
How well do you know your Lookups? [Quiz]

So you think you know VLOOKUP formula? Well, test your knowledge.

Click here to take our first ever quiz – on Look up formulas.

Continue »

Details about our Power Pivot Course [and a video for those of you not interested]

Published on Jan 30, 2013 in Excel Howtos, Power Pivot
Details about our Power Pivot Course [and a video for those of you not interested]

Hello folks,

If this article was a person, they would be schizophrenic. You see, it has 2 purposes:

  • Give you all the details about my upcoming Power Pivot course
  • Give you a solution to last week’s vacation days problem

Details about Power Pivot Course
Power Pivot, an Excel add-in makes it easy to connect, analyze & visualize massive amounts of data. This course aims to teach you how to use Power Pivot to analyze data, create advanced reports & prepare dashboards all from familiar interface of Excel. This is ideal for data analysts, reporting & MIS professionals, business analysts, managers & dashboard makers.

Continue »