In-Class Exercise: Analyzing & Managing Data

The following exercises are based on conditional formatting, basic equations, and filters. For this exercise, open the in-class workbook, make a copy, and follow the instructions. You can find the in-class workbook here: (Starter-Workbook)-Class-Analyzing-&-Managing-Data.xlsx

Be sure to rename it something like “(Your-Name)-Class-Analyzing-&-Managing-Data”.

Exercise 1 - Company Inventory

  1. Navigate to the Company Inventory sheet and review the data. Your boss has given you a list of customer orders. He wants you to sort through the customer data to show the top steel buyers.

  2. At the bottom right of the table, sum the total quantity of material purchased (Cell C14).

  3. Highlight all orders where the material purchased = "Steel". HINT: Use conditional formatting

  4. Add filters in columns A through C in row 2

  5. Add a filter that only shows customers who ordered more than 15 tons of material

Exercise 2 - Analyze Materials Purchased

  1. Navigate to the Analyze Materials Purchased sheet. Now that you have proven your boss your skills, he has given you a new task with more customer order information

  2. Format the table by adding borders, bolding the title, merging cells A1:E1, and adding colors to distinguish between the title, labels, and data. The goal with this is to clearly and neatly display the data in the table

  3. Add a color scale to the values in the table using conditional formatting (Cells B3:E26). Have the lower numbers be a lighter shade and the higher numbers be a darker shade

  4. Add filters to the table.

  5. Use the filter to display the customers that ordered between 20 and 75 tons of material for Order #2 (column C)

Exercise 3 - Temperature Conversions

If you do these steps out of order, your formulas will look weird when the filters are applied first. Wait until the end to add filters.

  1. Navigate to the Temperature Conversions sheet. Your boss loved your work so much that now you are the lead intern for the Excel Spreadsheet division. He has now given you the task of analyzing the temperatures on one of his work sites to ensure building safety

  2. In cell C3 write in the equation that will convert from Fahrenheit to Celsius found in the "Conversion Equations" table. Convert the rest of the column to Celsius

  3. In cell D3 write in the equation that will convert from Celsius to Kelvin found in the "Conversion Equations" table. Convert the rest of the column to Kelvin

  4. In the "Statistical Data" table, find the average, max, min, standard deviation, and median from the values of Temp(F), Temp(C), and Temp(K) in the "Daily Temperature Data" table

  5. In the "Daily Temperature Data" table, highlight all temperature values at or below freezing in the Temp(F) column (as there are regulations against working in freezing temperatures). As a reference, freezing for Fahrenheit is 32 degrees

  6. Now, add filters to the "Daily Temperature Data" table. Only show dates that are before 9/22/2024

Turning in/Rubric

REMINDER - For this class, you will only turn in the links to your Excel files. You will get a 0 for this assignment if you turn in an Excel file or a link that is not shareable.

  1. Make sure your file is saved to your OneDrive folder.
  2. On the top right, click the share button --> share --> settings
  3. Click "anyone" at the top, then underneath "More settings", change "can view" to "can edit". Then click apply.
  4. Copy the link, then turn it into Learning Suite in the feedback box for that assignment.

Rubric:

Item Points Possible
Total
 5

The following is not a part of the rubric, but specifies how you can lose points. For example: if you fail to share your link correctly.

Reasons for Points Lost Amount
Link shared incorrectly -10%
Turned in late (per week) -10% (up to -50%)