Weighted Averages in Databases and Spreadsheets

Dr. Marcovitz

When using a database or a spreadsheet to create a gradebook, you need to think about how you want your grades to be calculated. It is very important to make the technology help you do what you want to do, rather than letting the technology force you to change the way you do things.

This is especially apparent when calculating grades in a gradebook. Using the AVERAGE function to take a simple average of all of your grades is fairly easy, but it is unlikely that that is what you want to do. If you have 6 homework assignments, 3 quizzes, and one test, it is unlikely that each homework assignment is as valuable as the test in your grading scheme.

The Mathematics

The first step in making your gradebook work for you is to figure out (mathematically) what you want to do. This is independent of the computer. You would need to do this whether you were calculating grades with pencil and paper, with a calculator, or with a computer.

The simplest option is to take the average of all of your grades. You can either add up all the grades and divide by the number of grades or you can use an AVERAGE function if one is available to you. Note that the following formulas are mathematical expressions. Use the fomulas in the following sections for the exact formulas to enter into a ClarisWorks database, Microsoft Access database, or ClarisWorks or Microsoft Excel spreadsheet.

In the above example, the unweighted averages would be something like the following:

(HW1+HW2+HW3+HW4+HW5+HW6+Q1+Q2+Q3+T)/10

Equation 1 (mathematical expression; do not type into your database or spreadsheet)

or

AVERAGE(HW1,HW2,HW3,HW4,HW5,HW6,Q1,Q2,Q3,T)

Equation 2 (mathematical expression; do not type into your database or spreadsheet)

A more common option is to weight the grades. Weighting requires you to decide what percentage of the final grade each thing (or group of things) is worth. The simplest weighting is to assign a percentage to homeworks, a percentage to quizzes, and a percentage to the test. Note that if you have other categories of things, you can include those as well. It is important that your percentages add up to 100%. If we make homework worth 10%, quizzes worth 50% and the test worth 40%, you would use the following mathematical formula:

10% times (HW1+HW2+HW3+HW4+HW5+HW6)/6 + 50% times (Q1+Q2+Q3)/3 + 40% times T

Equation 3 (mathematical expression; do not type into your database or spreadsheet)

or

10% times AVERAGE(HW1,HW2,HW3,HW4,HW5,HW6)+ 50% times AVERAGE(Q1,Q2,Q3) + 40% times T

Equation 4 (mathematical expression; do not type into your database or spreadsheet)

A third option for calculating grades is to assign a point value to each assignment, add up the total number of points that the student received and divide by the total number of points that were possible. In this way, each assignment can be worth a different amount. For example, imagine the following point values for each of the above assignments:

HW1 is worth 10
HW2 is worth 20
HW3 is worth 20
HW4 is worth 30
HW5 is worth 20
HW6 is worth 30
Q1  is worth 100
Q2  is worth 150
Q3  is worth 150
T   is worth 500

For this, we have to add up all the points earned and divide by the sum of what each assignment is worth:

(HW1+HW2+HW3+HW4+HW5+HW6+Q1+Q2+Q3+T) divided by (10+20+20+30+20+30+100+150+150+500)

Equation 5 (mathematical expression; do not type into your database or spreadsheet)

or

SUM(HW1,HW2,HW3,HW4,HW5,HW6,Q1,Q2,Q3,T) divided by SUM(10,20,20,30,20,30,100,150,150,500)

Equation 6 (mathematical expression; do not type into your database or spreadsheet)

These are mathematical representations of what you have to do on paper, on a calculator, or on the computer. If you are having trouble with these formulas, note that you are having problems with the math, not with computer.

The next step is to translate these formulas into a form that ClarisWorks, Excel, or Access will understand. Once you have developed the formulas (a math problem, not a computer problem), this process is fairly straightforward.

Databases

In a database, assume that the fields are named with my abbreviations (HW1, HW2, HW3, HW4, HW5, HW6, Q1, Q2, Q3, T). Create a new field of type calculation. For the formula, be sure to click on the field names rather than typing them. If you type them, you might leave out spaces or single quotes or something else that ClarisWorks wants. It will work if you type the field names, but only if you get them exactly right.

Other things to watch for are balancing parentheses, commas and plus signs, and spaces. If you type an open parentheses, always type a close parentheses. Make sure they match and are in the correct places. Items in a function are separated by commas (for example, AVERAGE(A,B,C) or SUM(A,B,C)) while items being added without a function are separated by plus signs (for example (A+B+C)). ClarisWorks does not generally like extra spaces. In writing, we put spaces after commas and possibly before and after plus signs; in ClarisWorks, we do not.

Our formulas for the equations will be as follows. If you clicked on the field names, most of the differences between the formulas above and the ones below will be taken care of for you automatically. To get the appropriate function names, you can either type them in or click on them from the function list. If you click on them, be sure to delete what is between the parentheses because that is just an example.

('HW1'+'HW2'+'HW3'+'HW4'+'HW5'+'HW6'+'Q1'+'Q2'+'Q3'+'T')/10

ClarisWorks Database Equation 1

 

AVERAGE('HW1','HW2','HW3','HW4','HW5','HW6','Q1','Q2','Q3','T')

ClarisWorks Database Equation 2

 

10%*('HW1'+'HW2'+'HW3'+'HW4'+'HW5'+'HW6')/6+ 50%*('Q1'+'Q2'+'Q3')/3+40%*'T'

ClarisWorks Database Equation 3

 

10%*AVERAGE('HW1','HW2','HW3','HW4','HW5','HW6')+ 50%*AVERAGE('Q1','Q2','Q3')+40%*'T'

ClarisWorks Database Equation 4

 

('HW1'+'HW2+'HW3'+'HW4'+'HW5'+'HW6'+'Q1'+'Q2'+'Q3'+'T')/ (10+20+20+30+20+30+100+150+150+500)

ClarisWorks Database Equation 5

 

SUM('HW1','HW2','HW3','HW4','HW5','HW6','Q1','Q2','Q3','T')/ SUM(10,20,20,30,20,30,100,150,150,500)

ClarisWorks Database Equation 6

 

Access Databases

Microsoft Access does not have a function to a function to average or sum across fields within a record, so we cannot use equations 2, 4, and 6 in Access. This is only a minor inconvenience because it is easy to do the same thing with the other equations. In Access, the calculation can take place in a Query as a new column in the Query. If your field names have no spaces in them, it is not necessary to type the square brackets in the equations, but if your field names contain any spaces, you must include the square brackets.

 

FinalGrade:([HW1]+[HW2]+[HW3]+[HW4]+[HW5]+[HW6]+[Q1]+[Q2]+[Q3]+[T])/10

Access Database Equation 1

 

FinalGrade:.10*([HW1]+[HW2]+[HW3]+[HW4]+[HW5]+[HW6])/6+ .50*([Q1]+[Q2]+[Q3])/3+.40*[T]

Access Database Equation 3

 

FinalGrade:([HW1]+[HW2]+[HW3]+[HW4]+[HW5]+[HW6]+[Q1]+[Q2]+[Q3]+[T])/ (10+20+20+30+20+30+100+150+150+500)

Access Database Equation 5

 

Spreadsheets

Many of the issues for databases and spreadsheets are similar. The functions will be identical and issues of spaces, commas, plus signs, and parentheses also will be the same. The difference in a spreadsheet is that you don't have fields and field names, you have cells. Instead of creating one formula for the entire database or Query, you will create a formula for a single cell and you will use the Fill Down option in the Calculate menu (for ClarisWorks) or the Edit menu (for Microsoft Excel) to copy that formula for all other rows of your spreadsheet.

To create a formula, click in the first cell that needs the formula. You will probably have a column for your averages on the far right of your table (though I put the final averages next to the names of the students to assist me in filling out grade reports). Assume that this average is in column M and the grades are in columns C through L. Assume also that row 1 is for headings, so the first student's data is in row 2. Thus the formula for the first student's average will go in cell M2. Click in cell M2. Type the equals sign = to indicate that you are putting in a formula. The equations will be as follows:

 

=(C2+D2+E2+F2+G2+H2+I2+J2+K2+L2)/10

ClarisWorks and Excel Spreadsheet Equation 1

 

=AVERAGE(C2,D2,E2,F2,G2,H2,I2,J2,K2,L2)

ClarisWorks and Excel Spreadsheet Equation 2

 

=10%*(C2+D2+E2+F2+G2+H2)/6+50%*(I2+J2+K2)/3+40%*L2

ClarisWorks and Excel Spreadsheet Equation 3

 

=10%*AVERAGE(C2,D2,E2,F2,G2,H2)+50%*AVERAGE(I2,J2,K2)+40%*L2

ClarisWorks and Excel Spreadsheet Equation 4

 

=(C2+D2+E2+F2+G2+H2+I2+J2+K2+L2)/ (10+20+20+30+20+30+100+150+150+500)

ClarisWorks and Excel Spreadsheet Equation 5

 

=SUM(C2,D2,E2,F2,G2,H2,I2,J2,K2,L2)/ SUM(10,20,20,30,20,30,100,150,150,500)

ClarisWorks and Excel Spreadsheet Equation 6

 

Once you have created the appropriate formula for cell M2, highlight all the cells that need this formula and choose Fill Down from the Calculate menu. Note that for the cell numbers in the formulas, you can either click on the appropriate cells or type the cell names. It is not as necessary to click on cells because it is fairly easy to type the cell names correctly.

Note that the formulas are identical for ClarisWorks and Excel. Also, note that in equations 2 and 3, percents were used instead of decimals; i.e., 10% instead of .10 was used. Using the decimal equivalent is fine. If you use percents, be aware that Excel will automatically format your final answer as a percent. To fix this, highlight the cells that are formatted with percents and choose Cells from the Format menu. Under the Number tab, pick Number from the Category list and click OK.

Conclusion

Spreadsheets and databases make your job easier if you calculate grades. First you need to conquer any math fears you have so you can describe how you want to calculate your grades in a mathematical way. The translation to ClarisWorks is not difficult from there.

These examples are only some of the ways you might choose to calculate your grades. Databases and spreadsheets provide the flexibility to calculate grades any way you want. If you can describe it mathematically (or logically), you can do it in ClarisWorks.

Return to ET605 Home Page.


This page was prepared by Dr. David M. Marcovitz.

Last Updated: September 1, 2000