Find
Great Courses
Create
a Course

This course will become read-only in the near future. Tell us at community.p2pu.org if that is a problem.

Learn to receive imperfect data, clean it, prepare it, and put it to work

To work with data, a data wrangler often has to synthesize many sources of data, in a variety of different formats, and often with missing, corrupt, or otherwise imperfect data. Preparing your data, or "cleaning" it is one of the most time-consuming and dry tasks a data wrangler has to deal with, but also one of the most important. In this course, you'll pretend to receive some imperfect data, then prepare it for use, and use it to answer some simple questions.

P Time it should take to complete:
2 hrs

Start Challenge View Tasks

Calculate the Total Cost of Each Sale Made

Learn the VLOOKUP function

Now, we want to bring over our newly calculated product costs into the Sales spreadsheet, so that we know what the company spent to fill each order. To do so, we'll be filling in columns H and I in the Sales spreadsheet.

To calculate the Cost per Unit, we need to choose the appropriate cost, depending on the ID of the product sold. To do this, we'll be using the VLOOKUP function. This function looks up a given value in a table of values, and returns the corresponding result. It takes four arguments: the value to be looked up, the table to look up that value in, the number of the column (relative to the first) of the result to return, and a true/false argument having to do with what to do if the desired value is not found in the table. This last argument can mostly be ignored for now; you will usually just use "FALSE").

Put this formula in cell H6:

=VLOOKUP(D6,Cost!$B$8:$H$10,7,FALSE)

and press enter. This means that we are looking up the value in cell D6 (the product ID) in the table that is found in the Cost sheet running from cell B8 to H10. We use fully absolute references so that we can copy this formula to the rest of the column. Also note the syntax for referencing cells in a different sheet; as before, we could have written the "=VLOOKUP(" part of the formula, then used the mouse to choose the cells to refer to for the first two arguments. The third argument, 7, says to use the seventh column in the table B8:H10. Counting column B as "1", column H, holding the desired Total Cost value, is column number 7.

Copy the formula you just wrote, and paste it into the rest of the empty cells in the column, then calculate the total cost of each sale by multiplying the number of units by the cost per unit you just calculated. At this point, it might also make sense to calculate the total cost for the year, by summing the values in column I in cell I34, as you did in task 2. You should receive a value of $12,394.16.

Now that we have the Total Revenue and Total Cost for the year, we can calculate the company's profit margin in cell I35; simply divide the Total Revenue by the Total Cost. You should get a profit margin of 153%. In the next and final task, we'll learn how to break this figure out by month.