Here is chart listing formulas, when to use them and alternates.
- 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
These formulas are used to calculate numeric points and percentages. Note that arbitrary cell values are used in the formulas.
|Find Percentage||=A1/A$2 (A2 is total points)
|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.
|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)
|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.|
See the Formulas page for detailed information on how letter grade formulas work.
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")
Simple Letter Grades
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.
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.
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.
|1||Name||Totals||Percent||Intermediate||Final Letter Grade|
These formulas help you analyze student performance based on distribution of grades.
|Find number of instances of a letter grade "A" in a range||=COUNTIF(A1:G1,"A")|
|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
Last Update: May 18, 2011