All articles with 'homework' Tag
It looks up the first occurrence and returns corresponding data.
What if you want to find the last value?
Say, for example, you are looking at a task assignment list and want to know what is the last task assigned to employee Emp13?
We want to extract the task “Make amazing workbook”. Of course our good old VLOOKUP stops once it finds Emp13 and returns the answer as “Create intuitive workbook”.Continue »
Analyzing top n (or bottom m) items is an important part of any data analysis exercise. In this article, we are going to learn Excel formulas to help you with that.
Let’s say you are the lead analyst at a large retail chain in Ohio, USA. You are looking at the latest sales data for all the 300 stores. You want to calculate the total sales of top 10 stores. Read on to learn the techniques.Continue »
Few days ago, I asked you “How many hours did Billy work?” There were more than 100 responses with lots of innovative solutions.
So today, let’s examine various ways to calculate total working hours given start & end times of tasks. Please watch below video.
Calculating Bill’s total working hours (video)Continue »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
Today lets rescue John Doe from John_doe@email.com.
Extract first & last name from email address
Given an email address in the format
You need to extract first name & last name using formulas.Continue »
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:
- How to delete blank cells & rows?
- Dealing with blanks – case study
- Quickly filling blank cells in a table
- Extracting non-blank data from a list
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 »
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 »