USING SPREADSHEETS FOR GRADES

USING SPREADSHEETS FOR GRADES

1.	Download student names and Social Security numbers from the DUCK.
	1.	Gain personal access to the Duck using your pin number.
	2.	Click on Faculty and Advisors Menu
	3.	Click on List of Classes
	4.	Select the term and submit
	5.	Click on the CRN hyperlink for the class
	6.	Read and follow the directions in: "Importing a Comma Seperated [sic] File into Excel", which can also be printed from the DUCK class list. After you complete the instructions, save the file as an .xls file in the My Documents folder.
	7.	Close the Duck.
2.	Start Excel if it is not already running. Open the .xls file you just created that contains the student names. Autofit the A and B columns by double clicking at the division between the column headers.
3.	Using your syllabus and grade book, make a list of all items for which students receive a grade. List everything, including grades which are assigned at one time, i.e., attendance, homework (if it is a completion grade at the end of the quarter.) If there are multiple quizzes, homework assignments, or tests, list them all, preferably by naming them Test 1, Test 2, etc.
4.	Insert a new blank row at the top of the spreadsheet.
	1.	Select the first row by clicking on the number 1 at the left hand side.
	2.	Click on Insert, Row. A new row 1 should appear.
5.	Beginning in cell A1, key in column headers. Start with SSN, Name, then enter the list you created with all graded items for your class. If items are in sequence, such as Test 1, Test 2, etc., you can insert these quickly by keying in the first one, then clicking on the AutoFill handle and dragging. Excel will create a sequential list. Leave a blank cell when you complete a category, such as after all the quiz grades, or after all the test grades. The only exceptions are grades of only one item, such as an overall Homework grade. Leave no blank cell in that case. Press tab to move to the cells to the right, and shift+tab to move back to the left.
6.	Save the file by clicking on File, Save As. Make sure you save the file as an Excel document, and give it a name you can recognize later.
7.	Select cells C1 to the end of your entries in row 1. Change the orientation of the entries by:
8.	1.	Click on Format, Cells, Alignment.
	2.	Choose the orientation desired.
9.	For the first row that contains a student name, write formulas in the blank cells to average the section, such as the average of all quiz grades, the average of all the test grades. The average formula looks like this: =Average(range), where range is all the cells to be included. Example: the quizzes are in columns C through E, row 6, so the formula would be =Average(C6:E6).
10.	In the next blank column on the right, write a formula to calculate the final grade. Take into account how much each item is weighted, which is probably in the syllabus. Remember, all formulas start with an = sign. It is helpful to put parentheses in just to group the items in the formula.
11.	Copy the formulas down as many times as necessary to create a formula for each student. Save the file.
12.	As the semester progresses, enter student grades. During the first semester, double-check the grades to make sure the formulas are correct.


TIPS:	Format cells as needed for decimal places.
	AutoFit columns as needed.
	As the end of the semester draws near, you should be able to plug in imaginary grades and tell a student what they have to make on a final to make a certain grade. Just don't save any of these imaginary grades!
	If the columns are too wide to fit on the screen, use Freeze Panes. Click on the first cell to the right of the first student name, and just below the column headers. Click on Window, Freeze Panes. Excel should now always keep the column headers and the students names visible as you scroll through the worksheet.
	If a student withdraws from the class, select their entire row and shade the cells by clicking on Format, Cells, Pattern, then select a color.
	If you are feeling adventurous, try a Look up table that actually assigns the grades for you (see directions below the example.)

EXAMPLE:
This is only an example, your workbook may have different formulas, depending on your class syllabus.

The EXCEL 101 class syllabus lists the following:

Quizzes (3)	20%
Tests (2)	40%
Project	20%
Homework	10%
Final	10%

The spreadsheet would look like this:

Creating a VLOOKUP Table

Excel can automatically match values to a table and assign an additional value or text character by using the VLOOKUP (vertical lookup) function. However, the VLOOKUP table must be set up in the following manner:

1.	You must start at zero and increase.
2.	You must reference the lookup table with absolute references.
3.	There can be no blank columns in the lookup table.

Suppose a class had this grade scale:

A	90-100
B	80-89
C	70-79
D	60-69
F	Below 60

The VLOOKUP table would have to be created this way.

0	F
60	D
70	C
80	B
90	A

How it works:

Example: If a student made an 87, Excel begins comparing the 87 to the first column in the VLOOKUP table. 87 is greater than 0, so it moves to the next category; 87 is greater than 60; greater than 70; greater than 80, but not greater than 90, so it falls back one category and assigns a B to the student.

Formula syntax:

=VLOOKUP(value to be compared, lookup table, which column in the table contains the answer).

EXAMPLE:

Where B2 is the value to be compared, $E$1:$F$5 is the range of the lookup table written with absolute references, and 2 indicates to use the second column of the table to return a text or value.

Your final results should look like this: