Manage your Grades Electronically with Excel Skip Main Menu

Formula Appendix

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

This Page

  1. Key
  2. Calculating Numeric Grades
  3. Calculating Letter Grades
  4. Analyzing Student and Course Performance

Key

Top of Page

Calculate Numeric Grades

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 lowest 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 first highest score. To add more scores, add a LARGE function for each rank.
Average of Scores =AVERAGE(A1:G1)  
Weight by Percentage =.1*A1+.2*G1+... You must find the sum of each course component first, then multiply it by the decimal point corresponding to the percentage weights.

 

Top of Page

Calculating Letter Grades

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

Pass/Fail Formula

Possible Glitches

Simple Letter Grades

Adding the Plus and Minus

  1. 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 ("").
  2. 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.

Mock Student Spreadsheet with Letter Grade Results
  A H I J K
1 Name Totals Percent Intermediate Final Letter Grade
2 Picard, J. 112 93.3% A- A-
3 Riker, W. 106 88.3% B+ B+
4 Troi, D. 117 97.5% A A
5 Zellig,W. 83 69.2%   D

 

Top of Page

Analyzing Student and Course Performance

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

Formulas for Assessing Student/Course Performance
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)

 

Top of Page

Last Update: May 18, 2011