Formula Appendix
Here is chart listing formulas, when to use them and alternates.
This Page
Key
- A1,G1, I2 are generic cell references
- A$2 means a cell in any column in a fixed row.
- A1:G1 means any range in either a row or column
- .1,.2 are any two percentage weights
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. |
Calculating Letter Grades
See the Formulas page for detailed information on how letter grade formulas work.
Pass/Fail Formula
- =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
Possible Glitches
- 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")
Simple Letter Grades
- =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.
Adding the Plus and Minus
- 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.
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 |
Analyzing Student and Course Performance
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) |
Last Update: May 18, 2011