Return to discussion

Instructions for creating an EXCEL grade conversion table
Go to top of main page
Return to Step 8 in main page

Return to Main Index
Return to Site Map

NOTE: If you use my Grade Conversion Table template, just place the maximum points and the grade cut-off points into the appropriate cells for any test(s). The place the values under Grade Range Calc. Values Calculated into the adjoining cells under Grade Range Calc. Values Used. The template will automatically perform Steps 3-6 as described below for whatever test(s) you enter. Return to Step 8 in main page

1. Number cells in the second column (Number Grade column) in descending order, starting with 100. (For my table, A range = 100-90, B range = 89-80, C range = 79-70, D range = 69-60, F range = 59-0. I limit the keep size to one printed page by numbering cells down to 40 rather than down to 0.)
NOTE: If you use my Grade Conversion Table template,

2. Merge cells in the first column (Letter Grade column) that correspond to each letter grade range in the second column. I merge the first 11 cells for the A range (i.e., 100-90), the next 10 cells for the B range (i.e., 89-80), the next 10 cells for the C range, the next 10 cells for the D range, the remaining cells in the table for the F range (i.e., 59-40). Letter each letter grade cell appropriately (i.e., A, B, C, D, F). Grade Conversion Table template

3. Place the sum of the maximum points possible on the exams into the cell in the third column (Points column) next to the 100 For example. With grades for grades for Tests 1+2, 85+ 85 =170. Place the sum of the points for A cut-offs next to 90 (e.g., 96 + 70 = 139). Then place the sum of the points for B cut-offs next to 80 (e.g., 59 + 61 = 120). Continue the same process for the C and D cut-offs (e.g., 70 = 102 points, 60 = 85 points). (GCT for Tests 1+2)

4. Determine the point values for each number grade for grades 60 – 90.
Divide the difference between points for each grade cut-off by 10. It is in the table under Grade Range Calc. Values Calculated and under Grade Range Calc. Values Used. It is "value (3)". Value (3) may be different for each grade range (e.g. 1.000, 0.900, 0.900). Starting with the point value for the lowest grade in each grade range, add value (3) for that grade range repeatedly to determine the point values for successive grades in that grade range. Place these values next to their corresponding grades (e.g., points in the 60 – 70 grade range = 85, 87, 88, 90, 92, 94, etc.) I round off to the nearest whole number. This procedure produces a linear scale between each grade cut-off. Since value (3) may be different for each grade range, the slope of this linear scale may be different for each grade range.

5. Determine the point values for each number grade for grades 0-59.
Divide the number of points needed for "60" by 60. It is "value (4)". Subtract value (4) repeatedly from the points for number grade 60 to determine the descending point values for descending number grades, and place the point values next to their corresponding number grades. I round off to the nearest whole number (e.g., 85 /60=1.4167, "59" = 84 points, "58" = 82 points, etc.) This system creates a linear scale between grades 60 and 0. The slope of this linear scale may be different from the slope for the grade ranges above grade 60.

6. Determine the point values for each number grade for grades 91-100.
Because my tests are always difficult, I usually have a low point value for a grade of 90. Therefore, I use a graduated scale rather than using a linear system for grades 91-100. My rationale is that the difference in points on a test between grades of 89 and 90 should be the same as that point difference between grades of 90 and 91. However, the point difference between grades of 91 and 92 may be slightly more, the point difference between grades of 92 and 93 may be even more, etc. up to a grade of 100. This system creates a curvilinear scale between grades 90 and 100. The points scale has an increasing slope as the grades increase from 90 to 100. See Graph at GCT for Test 1. To determine the point values for grades above 90, I do the following.
    a. Find the point difference between grade 90 and grade 100. This is "value (a)" (e.g., for Test 1, (a) = 16). Subtract the point difference between grades 80 and 90 from value (a) (e.g., 16-10=6). Divide that number by 45. This is "value (b)" (e.g., 0.133).
    b. Start with the points for grade 90. Determine the points for grade 91 by adding value (4) from the 80-90 grade range to the points for grade 90 (e.g., 69 +1.00= 70.00 = 70).
    c. Determine the points for grade 92. Add value (3) plus value (b) to the points for grade 91 (e.g., 70 + 1.00 + 0.133 = 71.133 = 70).
    d. Determine the points for grade 93. Add value (3) + value (b) + value (b) to the points for grade 92. (e.g., 71.33 + 1.00 + 0.133 + 0.133 = 72.596 = 73)
    e. Determine the points for grade 94. Add value (3) + (b) + (b) + (b) to the points for grade 93. (e.g., 72.596 + 1.00 + 0.133 + 0.133 + 0.133 = 73.995 = 74)
    f. Repeat, adding another value (b) each time, up to grade 100. If the point value for grade 100 (maximum possible points on the exam) is not reached in the correct number of repetitions, adjust value (b) under Grade Range Calc. Values Used to attain a final point value close to the points for grade 100. The points for a grade of 100 remain as the maximum possible number of points attainable on the exams.

Go to top of this page
Go to top of main page
Return to Step 8 in main page

Instructions for converting to another instructor's grading system, such as when team-teaching

I team-taught a course where the other instructor wanted each of the four quizzes to be worth exactly 40 points. With this system, students could earn up to 160 points for the semester. This instructor wanted me to report grades on this scale, and the point cut-offs on each quiz for grades of A, B, C, and D were always 36, 32, 28, and 24 respectively. This means that the point cut-offs for each letter grade during the semester were the point cut-offs for each quiz multiplied times the number of quizzes given (e.g., Table A for one quiz, Table B for three quizzes). Here is what I did for the students’ quiz scores. Sample records of test scores. .

1. After each quiz, prepare the grade conversion table as above. Grade Conversion Table template
2. Add one column to the right of the Points column. (Table A)
3. Enter the correct multiple of each cut-off beside the corresponding letter grade cut-offs. (Table A), (Table B)
4. For grades equivalent to 60-100, add 0.4 consecutively up the column starting with the points for a grade of 60 (e.g., after three quizzes in (Table B), a grade of 60 = 3X24 = 72 points out of 120 possible points using the 40-point per quiz system).
5. For grades equivalent to 59-0, subtract 1/60 of the value for the previous grade (i.e., [points for grade of 59] = [points for grade of 60] - [1/60 points for grade of 60]; [points for grade of 58] = [points for grade of 59] - [1/60 points for grade of 59]; etc.) This can also be done by using the correct multiplication factor for each grade (i.e., grade on scale of 100 divided by 60). (Table C)

Go to top of this page
Go to top of main page
Return to Step 8 in main page

Return to Main Index
Return to Site Map

The materials on this site are licensed under CC BY-NC-SA 4.0 .

https://www.biologyofhumanaging.com/Figures/CC-BY-NS-SA%20image.jpg
Attribution-NonCommercial-ShareAlike

This license requires that reusers give credit to the creator. It allows reusers to distribute, remix, adapt, and build upon the material in any medium or format, for noncommercial purposes only. If others modify or adapt the material, they must license the modified material under identical terms.
Previous print editions of the text Human Aging: Biological Perspectives are © Copyright 2000, 1994 by The McGraw-Hill Companies, Inc. and 2020 by Augustine DiGiovanna.

View License Deed | View Legal Code