Lab 5: Exploring Excel Further

In this lab, you are going to create an increasingly sophisticated spreadsheet that keeps track of student grades. You may work on this lab in teams of at most two. Most of the commands that you need have been discussed in class. If you run into problems, look at the help index, or ask your instructor. Be sure to save your work after you complete each task!

Enter Raw Data

1. Start a new workbook. Put both of your names in the upper-left corner. Underneath, place a label showing that this is lab 5. Save the file as <initials>_lab5.xls where name are the initials of one of the students. For example, I would use JMB_lab5.xls. You should save the file on one of your floppy disks.
2. Input the raw data. Enter the following information in a table starting in cell A5:
   

1. Add a row to the bottom of this table labeled "max possible". This should contain the maximum possible points for each of the assignments. The two exams are worth 100 points each, and each lab is 10 points.

Assignment Statistics

1. Add another row that shows the average score achieved for each assignment. You may want to use the function wizard to help out with this.
2. Add one more row showing the standard deviation for each assignment.

Lab and Exam Averages

1. Add a column to the right "end" of the worksheet that sums the scores of the labs for each student. Make sure your column also extends through the "possible" entry, as we will need the total possible points to compute the lab percentage.
2. Add another column to the worksheet to calculate the overall lab percentage achieved by each student. The basic formula is: total points/total possible * 100. Note that you will want to use an absolute cell address to represent the total possible points (this is the sum from the key row).
3. Add a column for exam average. This will be the average of the two exams.

Overall Score

The overall score for the class will depend on the relative weighting of exams and labs. This may vary from course to course, so we want to separate out the weighting information in another section of the worksheet.

1. Create a "weighting" table. Find some free space in the worksheet (around cell A25 should work) and type in a small table:

2. Add another column to the student list to include a final percentage score. The final score is calculated as: lab pct * lab weight + exam avg * exam weight. You will need absolute references for the two weights.

Assigning Grades

The grade for the class will be based on a percentage grading scale. First, we need to make a table showing what the grading scale will be.

1. Create a "curve". Find some free space (cell D25 should work as a starting point). Enter the following table:

This shows the minimum score required for each grade.

1. Add a "grade" column to the student list. You should be able to develop a formula that automatically looks up the correct grade for each student. Look for help on VLOOKUP to see how this is done.

Pretty it up!

Now that you have all the information computed, go back and try to make it all look nice. Here are some suggestions, don’t feel you are limited to this listing.

1. Format numbers appropriately. Raw scores and percentages should have no decimal places. The averages and standard deviation should be shown to one decimal place. The weights in the weighting table could be formatted as percentages.
2. Set the column widths for a pleasing layout. One good way to do this is to use the "auto-fit selection option.
3. Visually separate scores from summary information. Format the cells of the key row so that there is a heavy line between the last student and the key. You might use similar formatting to set off the summary columns.
4. Adjust the alignment of the column headings to make the table more compact (try vertical alignments).
5. Sort the student information to put the students in alphabetical order. Make sure you highlight the entire range of information for each student, so the scores are carried along with the name.

Hand it in

When you are satisfied with the result, save your file on your floppy. You should also copy it onto your partner’s floppy so that both of you have a copy. Then one of you should enter manhattan and attach your XLS file to a reply to the appropriate assignment message.