Lab #9 Spreadsheets

Summary

In this lab, you will create an Excel spreadsheet that computes your grade in the class and charts your lab grade percentage. This lab is worth 25 points. Read the whole lab before starting, because there is useful information at the bottom also.

First Things First

Depending on the system you are using, you'll find Excel under "Programs" or "All Programs" and then "Microsoft Office". Look around if the names don't match exactly or ask someone for help.

Requirements

You are to make a spreadsheet such that it has all the relevant information about your grade. It should include the following:

• Name your file grades.xls. Excel 2007 might try to make you save it the file as .xlsx. For the file type, choose "Excel 97-2003" and you will be able to save it as .xls.
• Format your spreadsheet more or less like the picture above. You don't have to use the exact same colors, but they should be somewhat similar. The borders surrounding particular cells should match. The headings should be bold.
• The percentages of each lab, project, and exam should be computed based on the two cells to the left of each percent. They should be computed to one decimal place. Ask someone if you're not sure how to format numbers to one decimal place.
• The percentages on the left should be "conditionally formatted" such that percentages below 60 are red and those 90 or greater are greenish. You have to use conditional formatting. We will check that the percents change color with different scores.
• For the assignments that haven't been graded yet, just put any number for your score. Maybe even what you expect to get, so you can extrapolate your grade.
• Name the cells containing the total percent for each section (in the picture above, D12, D19, and D24). Then use those names to compute the overall percentage on the top right something like this:
  
  =LabGrade * 0.30 + ProjectGrade * 0.45 + ExamGrade * 0.25
  
  
• When making the graph, choose a Line chart. Pick the chart that most resembles the one above. The chart should have a similar title and labels for the x and y axes. To get the x axes to use "Lab 1" and so forth as labels, you have to select both sets of cells as pictured below. Hold Ctrl while selecting the relevant cells.
  
  
  
  
• If you made a graph with a legend, you can delete the legend by clicking on it and pressing delete.

The relevant buttons to perform the various functions are shown below:


If you don't see the buttons in your version of Excel, you can find most of what you need for formatting under the Format menu and the Cells... option. In Excel 2007, you have to choose Home first and then Format. To make a chart, choose Chart... under the Insert menu after having selected the data you want to graph. As with all software, poke around the user interface or ask someone if you can't find what you need.

Submissions instructions

Turn in grades.xls here. When you submit, you will get a copy of your file on the screen as usual. Since Excel spreadsheets are not stored as text, you will see what looks like gibberish. That's OK!