In the ACCY department, we curve courses to an average of 3.3 grade points (B+). I had over 70 students across three sections in my ACCY 301 course for the fall of 2021, and wanted the accuracy of using an algorithm to calculate and apply the curve rather than trying to do it by hand.

Here are the steps.

1. Downloaded points from Compass (learning management system). I named this tab All Points. It contained all of the assignments and all of the points that students earned in the assignments. It also had a column showing the total points that each student earned.
2. Created a new tab called GPAPoints. Created a table in that tab with two columns:
   1. A column called Points. This column was numbered sequentially from 600 to 1000.
   2. A column called GPA points. This column showed the GPA points that would be assigned based on the points in the Points column. For example, 600 points = 0.7 GPA points. 1000 points = 4.0 GPA points.
3. Created a new tab called LetterGrade. Created a table in that tab with two columns:
   1. A column called Points. Like on the prior tab, this column was numbered sequentially from 600 to 1000.
   2. A column called Letter grade. This showed the letter grade that would be assigned based on the Points column.
4. In the All Points tab, made adjustments to the total points per the syllabus. The syllabus contained a provision to drop the lowest quiz score, so I did this in Excel. Then I created a column called Adjusted Total, and wrote a formula in that column to subtract the lowest quiz score from the total points that had been downloaded from Compass. The total here for each student is their total as downloaded from Compass, minus their lowest quiz score.
5. Created a Curve column, next to the Adjusted Total column. Then next to the last header of Row 1, I created a cell that would be used for the points of the curve. In row 2 of the Curve column, I wrote a static formula that enters the amount from the Row 1 cell (then copied this formula down the column). Currently, the total is the same as for Step 4. This will change in Step 10. The static formula looks like this:
   1. $AD$1
6. Created Curved Total column, next to the Curve column. Wrote a formula to subtract the Curve points column from the Adjusted Total column. Currently, it just carries forward the total from Step 5 but it will change in Step 10.
7. Created a GPA Points column next to the Curved Total column. Wrote the first VLOOKUP formula here. I looked up the value from the Curved Total column in Step 6 to the table in the GPAPoints tab from Step 2 above. The formula in the first cell looks like this:
   1. =VLOOKUP(Z2,Table13[#All],2,TRUE)
   2. After copying down the formula to the entire column, it looks up the value from the Step 6 and enters the appropriate GPA points from the table in Step 2.
8. Created a Letter Grade column, next to the GPA Points column. Wrote the second VLOOKUP formula here. I looked up the value from the Curved points column to the table in the LetterGrade tab. The formula in the first cell looks like this:
   1. =VLOOKUP(Z2,Table134[#All],2,TRUE).
   2. After copying down the formula to the entire column, it looks up the value from step 6 (yes, you read that right) and enters the appropriate letter grade from the table in Step 3.
9. Calculate average GPA points. At the bottom of the GPA Points column, I calculated the average of the GPA points.
10. Enter and adjust the curve. Here’s where it gets fun. I played around with the cell from Step 5, Row 1 (the curve that is given to all students in Step 6) in order to get that average GPA points calculated in Step 9 3.3. Once it is 3.3, all of the students have been assigned their appropriate letter grade.
11. Finally (and not relevant here but still might be useful to someone new to grading): if any student was less than one point away from the next grade “level,” (ie, less than one point difference between a B+ and an A-), I added the necessary fraction of a point to bring it up to that level. Four students qualified for such adjustments this semester.

All of these steps enabled me to only change the cell from Step 5, Row 1 (the curve) in order to assign the correct letter grade based on the average GPA points being 3.3.