Learn Moore | Ryan W. Moore

Excel Homework

For this homework, you'll take the data from this file here and use Excel to calculate some useful things about it.


The homework is due by 11:59PM Thursday, February 26th. You should submit it by emailing me. It's worth 109 points total.


Here's what you need to do, broken up into recommended steps. The point value of each step is given at the end of each step.

  1. Looking at the data, there are two sections. One for rings and one for earrings. You should use a formula to calculate the total in stock #, average price, minimum price, maximum price, and median price (hint: use the MEDIAN function) of each type of inventory (rings and earrings). So, you should end up with 10 formulas. Format the prices to be dollar values. This step is worth 15 points.
  2. Add in a total ring inventory value cell, with a label cell to the left of it. This cell should show the combined worth of all the rings. E.g., 3 rings at $10.00 each has a combined worth of $30.00. Worth 10 points.
  3. Do the previous step again, but show the combined worth of all earrings. Worth 2 points.
  4. Make a "Grand Total Inventory Value" cell that shows the value of all the inventory (both rings and earrings). Worth 2 points.
  5. Using embedded IF functions (i.e., an IF inside of an IF) have the Stock Level cells say either "1", "2", or "3" depending on whether the corresponding items "quantity in stock" is low, medium or high. Low is any number < 5, medium is a number < 20, and high is any other number. This way, if the "quantity in stock" value of a cell changes, the stock level value will update automatically. This is worth 30 points.
  6. If you have difficulty and can't get 3 different stock level values to appear (low, medium, high) then you can instead do only 2 values (low, high). You'll only get 20 points instead of 30 points though.
  7. Add conditional formatting to the stock levels, to show red circles if the stock level is low (1), yellow if it's medium (2), or green if it's high (3). Have Excel hide the values in the cells, so that only the colored shape appears. Do /not/ just make the cell small so that the number can't be seen. Worth 5 points.
  8. Create a chart that shows something useful about the data. You should use your own judgment in what makes sense. Give it an appropriate style/color and an appropriate title, as necessary. Worth 15 points.
  9. Create a histogram (using the ring data). The histogram should show how many products use gold, how many use silver, and how many use base. You may have to add some extra columns or information to the worksheet, to get the histogram numbers. For example, you could first insert a column which displays 1 if the item is gold, 2 if its silver, and 3 if it's base. Then, using the FREQUENCY array function, you could create the counts of how many items use each type of metal. Then using those numbers, you can make the histogram. Be sure to give the histogram appropriate colors (in other words, I want the gold bar to be a gold color, the silver bar to be a silvery color, and the base bar to be some other color). Worth 30 points.


If your charts are particularly nice, I am willing to give a few bonus points.