Introduction to Spreadsheets >> Introduction to Data Analysis Using Excel
1. Download the file “Store Sales 2011.txt” (you may need to right-click and select “save link as…”) Use the data in this file for the remainder of the assignment. Note that the date in the file is in the mm/dd/yyyy format, so while reading it into Excel please be vary of that.
Store-Sales-2011.txt
The format used in the “Store Sales 2011.txt” file is:
- Fixed width
- Space delimited
- Tab delimited
- Comma delimited
2. Import the file into Excel using the correct file format from the previous question. How many rows of data does the file contain?
Hint: Do not count the top (header) row.
2002
3. Sort the data by Order Date (oldest to newest) and thenby Order Quantity (largest to smallest). After the data is sorted, what is the Order ID of the order in the 3rd row of data (i.e. spreadsheet row 4)?
17058
4. Using cell references, how could you calculate the unit price of the order in the first row of data?
- =D2/E2
- =E2/D2
- =21/845.32
- =845.32/21
5. Insert a new column to the right of the “Sales” column, and type the name “Unit Price” as the header. Using cell references, calculate the unit price of the order in the first row, then copy and paste that formula down to the remaining rows.
Rounded to 2 decimal places, what is the Unit Price of the last row of data? (Do not include a dollar sign or commas in your answer)
30.28
6. What is the largest Unit Price in this dataset?
Hint: Use the MAX() function here.
7122.17
7. How many orders were sent by Delivery Truck? Assume each line is counted as a single order.
Hint: Use the COUNTIF() function here.
259
8. What is the total shipping cost of all orders sent by Delivery Truck?
Hint: Use the SUMIF() function here.
11973.56
9. If you wanted to know the range, or the differencebetween the highest and lowest value, of the shipping costs in this dataset, whichformula could you use?
- =MAX(H2:H2003)-MIN(H2:H2003)
- =SUM(H2:H2003)
- =MIN(H2003)-MAX(H2003)
- None of the Above
10. What is the average shipping cost (to 2 decimal places) of all orders sent by Delivery Truck?
Hint: Calculate the average by taking total cost divided by number of orders.
259
