Formula Appendix

Here is chart listing formulas, when to use them and alternates.

This Page

These formulas are used to calculate numeric points and percentages. Note that arbitrary cell values are used in the formulas.

Issue	Formula Syntax	Notes
Add Scores	=SUM(A1:G1)
Find Percentage	=A1/A$2 (A2 is total points) =PRODUCT (A1,1/A$2)	1. Use "Percentage" number formatting to convert ratio to a percent. 2. Use the $ in cell references to make sure formulas refer to the same formula. For instance A$2 would hold the reference to row 2.
Drop Lowest	=SUM(A1:G1)-MIN(A1:G1) =SUM(A1:G1)-SMALL(A1:G1,1)	You can subtract the result of either the MIN function or the the first smallest number (SMALL function).
Drop Lowest 2	=SUM(A1:G1)-(SMALL(A1:G1,2)+SMALL(A1:G1,1))	This formula subtracts the second smallest score and the first smallest score from the total. To subtract more scores, use a SMALL functions for each score place to remove.
Find Highest Score	=MAX(A1:G1) =LARGE(A1:G1,1)	You can use either the MAX function or find the first largest number (LARGE function).
Find Highest 2	=LARGE(A1:G1,2)+LARGE(A1:G1,1)	This formula adds the second highest and the frist highest score. To add more scores, add a LARGE function for each rank.
Average of Scores	=AVERAGE(A1:G1)
Weight by Percentage	*=.1A1+.2G1+...*	You must find the sum of each course component first, then multiply it by the decimal point corresponding to the percentage weights.

See the Formulas page for detailed information on how letter grade formulas work.

=IF(A2>=70%,"PASS","FAIL")
Note that the desired grades "PASS" and "FAIL" are marked by quotes in the formula to indicate that text should be written into the cell

If you use "70", instead of "70%", the passing grade will be calculated as 7000%
If your numbers are not formatted as percentages, then the formula should be =IF(I2>=.7,"PASS","FAIL").
If you see "True" or "False" in a cell, then your formula may be missing a condition in the syntax.
The formula could be rewritten as =IF(A2<70%,"FAIL","PASS")

=IF(I2>=90%,"A",IF(I2>=80%,"B",IF(I2>=70%,"C",IF(I2>=60%,"D","F"))))
Tip: As you come to a nested formula, added a set of empty parentheses, then fill in the formula.

Insert the following formula into cell after the final percentage.
=IF(I2>=95%,"A",IF(I2>=90%,"A-",IF(I2>=88%,"B+",IF(I2>=83%,"B",IF(I2>=80%,"B-","")))))
This formula calculates grades down to B-. If the score is not a B-, then the cell is left blank ("").
Insert this formula after the first formula.
=IF(J2<>"",J2,IF(I2>=75%,"C+",IF(I2>=70%,"C",IF(I2>=60%,"D","F"))))
This formulas checks to see if there is a score in Column J (J2<>""). If there is a score, it copies the value in cell J2; otherwise it calculates grades for C-F.

Here is a copy of the mock spreadsheet showing the results of both formulas.

These formulas help you analyze student performance based on distribution of grades.

Issue	Formula Syntax
Mean, Average	=AVERAGE(A1:G1)
Standard Deviation	=STDEV(A1:G1)
Find number of instances of a letter grade "A" in a range	=COUNTIF(A1:G1,"A")
Median	=MEDIAN(A1:G1)
Correlate Scores between assignments. A score of 1.0 is perfect correlation. Scores of .5 or better may be acceptable. Lower scores may indicate a problem in the construction of one of the assignments	=CORREL(A1:A10,B1:B10)

These pages also discuss using Excel as a gradebook.

Last Update: May 8, 2006