JavaScript DHTML Menu Powered by Milonic



Create A Gradebook

by Tzilla Kratter, David Yellin Teachers College, Jerusalem

 

Remarks and questions please e-mail kratter@mofet.macam98.ac.il

I hope the following handout that I prepared for my students will help you keep record of your students' grades. It is actually quite simple.

Probably the most recognizable difference between a word-processing document and a spreadsheet is that a word-processing document uses the paragraph as the standard method of presenting information, and the spreadsheet uses rows and columns. The reason is simple—most data entered onto a spreadsheet is numerical and is most easily read, understood, and manipulated when the numbers are presented their way. Vocabulary:

arrow key ( actually more like the plus sign or double arrow ),cell, column, data, entry, formula , formula bar, header, range, right-click, row, shortcut menu, spreadsheet,

Entering information

1. Open Microsoft Office » Microsoft Excel . You are in a worksheet with columns headed by letters and rows headed by numbers. Every cell has a different address. (Always mention column first e.g. B9, C5)

A quick look at the tool bar

Some of the buttons do not appear in Word6, (e.g. the Greek sigma = automatic sum button), function wizard, centre text over several columns etc.

Special feature

Every time you right click a cell you get a pull down menu (shortcut menu)

2. Click cell A1 to select it. ( It is highlighted and so active).

3. Type 9th grade . Notice that this information also appears in the formula bar as well as in cell A1.

4. Click ENTER. Or click on the tick off sign on the formula bar. Only now is the text really in cell A1.

5. Press ENTER (or right arrow key on keyboard). Or click on cell A2.

6. In cell A2, type the name of your class; for example, Science, English, or Math.

7. Press the down arrow key (or ENTER) to go to cell A3. Type in 1st semester.

8. Press enter three times to go to cell A6.

Add headers

Add headers for the types of entries you need.

1. Type Last Name. Go to next cell - either with the arrow key or Ü and type First Name.

 2. Repeat #1 but type the following items in the following cells : HW-1, : HW-2, : HW-3, Quiz-1, Mid-term Text, Average Score, Grade.
 
  Formatting text Formatting text in Excel is simple and easy and gives you the opportunity to add

visual enhancement to the spreadsheet.

 1. Highlight cells A1 through I1 by clicking A1 and dragging to row I1. Choose Arial Size 16 and Bold.

2. Click on centre text over several columns etc. in the tool bar. The text is now in the centre.

3. Go to cell A2. And repeat step #1 but choose Arial Size 14 and Bold.

    Repeat step #2
4. Go to cell A3. Repeat #1 but choose Arial Size 12 and Bold.

Repeat step #2.
 
 

Let's fit the entries in A6.

1. Highlight cells A6 through I6 by clicking A1 and dragging to row I1.

2. Right-click the selection.

3. In the Shortcut menu, click Format Cells Ü Click the Alignment tab

4. Under HorizontalText Alignment select wrap text. Click OK.

5. Select row 6 by clicking on the Row Header (the numbered gray area to the left of row 6). Click the Bold button and the Italic button. The text is now bolded and italicized.
 
 

  1. HW. In the short cut menu change the colour of the fonts, the border and style of lines.
Fitting the entries into the cells and adjusting the size of the columns -

1. Select columns A through I by clicking the Column Header for column A and dragging across to column I. Selected columns are highlighted.

2. Move the mouse pointer over the border between any two column headers until it changes to a double arrow. Double-click to automatically get the best fit for all the columns.

At this point your worksheet should look similar to this (your Excel window and text look slightly different than my example below):

Note When ######## appears in a cell, the cell is too narrow for the data to be displayed. You should resize the column.
 
 

Completing the data entry

In A7 type in the name of one of your students and his/her scores. Continue to type names A8 through A14.

In cell A16 type Class Average

Click the Sort Ascending button in the tool bar (or go to Table and Click on pull down menu »Sort » Ascending)
 
 

Additional cell formatting

1. Select cells A6 through I14.

2. Right-click the selection, then click Format Cells in the shortcut menu.

3. Click the Border tab.

4. In the Style box, click the "Medium-Weight Solid "Line.

5. Click on all the Outline buttons.

6. Click OK.

Adding formulas A. Class averageformula for each homework assignment , the quiz and test. 1. Click cell C16

2. Type =average(C7:C14), that is =average(C7 colon C14)

 3. Click ENTER or on the tick off sign on formula bar. In cell C16 you now have the class average for HW-1

You could type the formulas in cells D16 through H16. But that would take some time, and you might make some mistakes. Excel allows you to copy the formula while adjusting it for each column.

1. To fill all of the other cells , go to cell C16, right click on it and select copy.

2. Go to D16, right click and select paste.

3. Go to E16, right click and select paste

4. Continue until you have all the averages

B. Average score for each individual student

You need a formula here that adds up the points for all of the work and divides by the number of possible points. (If the mark you gave wasn't out of 100 you could convert it to a percentage later) by highlighting I7 throughI14 and clicking on % - the percentage button on the tool bar)

1. Click cell I7. (the Average Score cell)

2. Type =sum(C7:H7) that is =sum (C7 colon H7).

3. Select cells C7 through H7. You now have the sum of all the grades.

4. On formula bar add /6. (this means :total divided by number of items such as HW, quiz and test)

Click ENTER or click on the tick off sign on formula bar. You now have the average score of the first pupil on your list.

    1.Click on I7.

    2. Right click and select copy

    3. In cell I8 right click and select paste

    4. Continue until you have the Average score for each student on the list

Save your file once more !
 
 

C. Different Percentage for different components
Suppose you want every student's grade to be made up from various components, e.g. 30% for their HW assignments, 20% for their Quiz mark and 50% for the Mid-Term Test.


 
 
 
 

1. Copy from the existing spreadsheet cells: A1 through A14 and dragging to row

H1

2. Open a new spreadsheet and paste the above rows and columns.

3. Add headers: HW 30%, Quiz 20%, Mid-Term Test 50% and Final Grade.

4. Click cell I7

5. Type = (average(C7:F7))*0.3

6. Click ENTER or on the tick off sign on formula bar. You now have the average of all the HW assignments of the first pupil on your list.

Once again you can copy the formula.

Repeat the same steps but now your formula is located in J7 and is =G7*0.2

Repeat the same steps but now your formula is located in K7 and is =H7*0.5

Repeat the same steps but now your formula is located in L7 and is =(sum(I7:K7))*10

If you want whole numbers for the final score, e.g. 93 instead of 92.87 and 86 instead of 86.26, Highlight L7 through L14, right click » Format Cells » Number » Category » Number » and in Decimal Places write 0.

Please pay attention to the formula and the various brackets

(Go to L7 and write) = I7*0.3+J7*0.2+K7*0.5

Note: Calculation operators

The basic ones are: + addition; - subtraction; * multiplication; / division; & Connects two text values to produce one. For examples and meanings of operators click on ? (Help).
 
 

 Printing

1. Open File » Print Preview. If the worksheet is rather narrow and you would like to make changes, go back to File » Page SetUp and make the necessary changes (e.g. change left margine, top etc.)

2. If, on the other hand, the worksheet is wide and would not fit into one page, go to

File » Print » Properties, click on Landscape.

Copyright 1997 - ETNI
DHTML Menu By Milonic JavaScript