Skip Menu

Sections 1-4: Getting Started | Sections 5-6: Range Names & VLOOKUP
Sections 7-9: Averaging | Sections 10-12: Post Averaging

Using Excel to Manage Grades

7. Entering the Function to Sum or Average

8. Weighting Exams or Adding a Fixed Amount to a Score

9. Functions for Overall Class Average & Number of Students Averaged

7. Entering the Function to Sum or Average

^ Back to Top of Page

Using the Sum Function for Raw Scores:

After all the scores have been copied to the MasterList worksheet you can create your overall sums, if needed. There are a number of ways to do this; here are several:

Use the AutoSum function button on the Excel Toolbar. (It looks like a capital sigma.) This function automatically creates the formula for you. When you highlight a cell (the one where you want the total) then click the AutoSum button Excel will try to anticipate what you want to sum. It will type in =SUM( then highlight the addresses of the cells it thinks you want. If what the program chose is OK just hit the Enter/Return key to accept it. If not, you can correct the cell addresses by highlighting the correct cells then hitting the Enter/Return key.

Type in the arithmetic formula yourself. =a2+b2+c2+d2 is an example.

Type in the function itself. =sum(a2:d2) is an example. It will return the same result as the arithmetic formula above.

No matter which method you choose this will fill only 1 cell. You must copy the formula into the other cells. Use the Fill Handle method described under the VLOOKUP function to do this.

Using an Averaging Function for Raw Scores, Percent Scores, etc.:

If you want to average scores you can use the AVERAGE function. There are several ways to create an average. Click to highlight the cell where you want the average displayed:

Type in the function. You can type it in in several ways:

=average(a2,b2,c2,d2)

Note the commas instead of + signs.

=average(a2:d2)

This is a shortcut for the above list.

=average(a2,d2)

This will skip 2 columns of data (B and C).

If you typed it in correctly you will get an average (with decimals) in the cell UNLESS one of the scores was missing. In this case, you will get a #N/A as an average.

You can use the Fill Handle method to copy the formula into other cells. See VLOOKUP.

Use the Paste Function button on the Excel Toolbar (it looks like a lower case "fx"). Click the Paste Function button then click Statistical. Click AVERAGE in the Function Name box. Click OK. A Dialog box will open. The top box will be highlighted and contains the "guessed at" cell locations.

If they are correct click OK. The box will close and the formula will calculate the score and place it in the cell.

If the "guessed at" cell locations are incorrect you can type in the correction or highlight the correct cells. If you need to see the spreadsheet you can click on the Collapse Dialog box button that is located to the right of the Number 1 line (it looks like a checkerboard). Now just select the range you need in any student's row. Click the Collapse Box again to restore it to its original size. Click OK.

Note: The AVERAGE function is smart enough to know if you have a row of Column Heading labels--it will not include it in the range.

Use the Fill Handle method to copy the function into other cells.

For any of the methods of averaging you will get numbers to the right of the decimal point. The number you get will depend on the width of your column. If you want to convert these values into whole numbers you can use the ROUND function below.

Using the Rounding Function:

The rounding function can be used alone or in conjunction with the averaging function. This will eliminate the need to calculate the average in one column and the rounded average in another.

The rounding function by itself is as follows:

Rounding Function

Example

=round(cell,#digits)

=round(g2,0)

where cell is an address, such as, g2, and #digits is the number of digits you want to the right of the decimal point. If you want to round to a whole number use 0 as the number of digits.

Here is an example of the combination of averaging then rounding:

=round(average(d2:g2),0)

This can be typed into a cell and the integer version of the number calculated by the average function will appear.

Use the Fill Handle method for copying the function into other cells.

NOTE: An average, or a rounded average, will not be calculated for a student who is missing a score on one or more of this exams. (He will have a cell with #N/A in it.) The result will be #N/A.

8. Weighting Exams or Addeing a Fixed Amount to a Score

^ Back to Top of Page

How Weighting is Used:

Some instructors want to weight their exams, e.g., the final exam counts twice as much as each of the other exams. This can be done within the summing or averaging formulas or you can produce a new column of weighted scores.

Here are some examples of weighting and the formula you would type into the cell:

Adding a Constant Number to a Score:

Occasionally an instructor wants to add in a number to each score. Uually this is because he feels the class average for the exam was too low.

You will need to create a new column for this data and label it something like Exam1+5, Quiz2+3, etc. A formula for the new column could look like this:

=f2 + 5

When you do your summing or averaging be sure to use this column instead of the original data. Also, you will not be able to use cell ranges for addresses; you will have to use arithmetic versions:

Use =average(d2,e2,g2) to skip score in cell f2 and use g2 instead.

9. Functions for Averall Class Average & Number of Students Averaged

^ Back to Top of Page

Producing an Overall Class Average:

If you want to produce an overall class average (an average of each student's average or sums) you can use the AVERAGE function again. Here are the steps you need to produce this average.

In the column of the student's averages you may find some #N/A values instead of a score. Delete these from the column.

Go to the bottom of your spreadsheet data, then SKIP A FEW ROWS (you need some blank space after the student data so that the program will know where the end of the data is located).

In the same column as the student averages, type in the function. You can use the same methods as described in the averaging section above. You may want to use the rounding function to round the overall average to 2 decimal points.

An example of this is: =round(average(h2:h599),2)

Counting the Number of Students Averaged:

If you have produced an overall average you will have deleted the #N/A values from the student average column. If you have not deleted them do so now.

The function you will want to use is called COUNT.

Highlight the cell below the Overall Class Average.

Click the Paste Function button on the Toolbar (it looks like a lower case "fx").

Click Statistical then click COUNT. Click OK.

The Dialog box will open showing you the "guessed at" cell locations. If it is correct, click OK. If not, you can type in any changes. Check the range carefully because if you have inadvertently left in a #N/A value the range will start with the next row pass the #N/A value instead of Row 2.

Note: The COUNT function is smart enough to know if you have a row of Column Heading labels--it will not include it in the range.

If you wish, you can simply type in the function, such as:

=count(h2:h599)

If you accidentally include the Heading label, it will ignore it.