All articles with 'homework' Tag
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 »
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 »
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 »
So you think you know VLOOKUP formula? Well, test your knowledge.Continue »
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.
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)
Are you ready for an Excel challenge?
Today, your job is very simple. Just find a pattern in a text and return corresponding value.
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 »