Overview
HW3resubmit
Fix mistakes in made on homework 3
For your re-submission, please write a summary of your updates, and list the steps of the operations you used in Excel to get to the right answer. You should submit a) an Excel file with your corrected work, and b) a Word file describing the updates (list your mistakes from last time and list the steps of how you've corrected for each).
I have already given you the answer key, so listing out the steps will earn you additional points - submitting only the correct Excel answers won't earn you additional points. You should not submit my answer key as your work - it is important that you go through the steps and understand why we use vlookup, how to use it correctly, and after we bring additional information to our grand sheet, how we analyze the data and show results.
For example, in your re-submission, say where you did wrong last time (you can copy/paste my comments for hw3), and list the steps like the following (in your own words) for the questions you did not get right last time:
Steps for Q1:
In sheet “Customer”, highlight cell A1 to C21, and right-click, use “Define Name” and name this range “CustTable”. Verify that the first column in this table is the identifier (CustomerID).
In sheet “Orders”, type “=vlookup(B2, CustTable,2,false)” in cell D2. This brings the names (in the second column of CustTable) of the customer with the customerID in B2 from CustTable.
Hit Enter, and then double click the lower right corner of this cell to auto-fill the column.
....
Homework directions:
Please finish the tasks in the above Excel file. When you are done, please save your work as HW3_YourLastName.xlsx (do not include special symbols in your file name please - otherwise I cannot view the file), and submit only your excel file. Please pledge your honor code in your Excel file by pasting this statement in sheet "Orders" and cell "G14" : "I confirm that I have typed in the vlookup functions in this file myself, and have done my own work generating the pivot tables and the chart."
No google forms this time.
Think of the tasks in HW3 as another typical transaction dataset that you need to work with in order to deliver some analytic insights. The difference is just that this time you will do some data work like importing information from other sheets and so on, before you can get to analyze the data.
- ///Note: After you have imported "OrderDate" using vlookup, it may show as some number like 40000's. This is not an error. You just need to format the cells into "Date" by right-clicking your mouse and do "Format Cells".
///Note: In sheet "OrderItems", each row represents one item sold.
- Learning objectives:
1) Learn how to use the vlookup function correctly;
- 2) Learn to analyze the data with Excel PivotTable;
3) Learn to use Charts in Excel.
- Comments made on homework 3 “Q2c incorrect. (-5)
Q1 did not fill the columns fully, formula is correct. (-2)
Q3 formula incorrect. Please pay attention to the common ID's in the two sheets. (-10)
Q4 pivot table tried with incorrect input data, and no chart. (-10)