Assume that the student name is A3: a762, and the score of a certain subject is G3: g762, with a full score of 150. There are two methods to divide the four equals levels:
1. Scores are calculated from high to low. The first 1/4 of the total number is A, the second 1/4 is B, the third 1/4 is C, and the fourth 1/4 is D, enter the same time in column H.
There are several formulas that can be implemented. below, only the H3 formula is given. You can drop down the other formulas.
= Lookup (rank (G3, G $3: G $762), count ($ G: $ g) * {0, 0.25, 0.5, 0.75} + 1, {"A", "B", "C", "D "})
= Choose (MATCH (rank (G3, G $3: G $762), count ($ G: $ g) * {0, 0.25, 0.5, 0.75} + 1 ), "A", "B", "C", "D ")
= Hlookup (percentrank ($ G $3: $ G $762, G3, 2), {0, 0.25, 0.5, 0.75; "D", "C ", "B", "a"}, 2)
= If (G3> percentile ($ G $3: $ G $762, 0.75), "A", if (G3> percentile ($ G $3: $ G $762, 0.5), "B", if (G3> percentile ($ G $3: $ G $762, 0.25), "C", "D ")))
= Char (64 + match (rank (G3, G $3: G $762), count ($ G: $ g) * {0, 0.25, 0.5, 0.75} + 1 ))
2. If the score is greater than or equal to 150, 0.9 (135 points and above) is a, and 150 less than 0.9 is greater than or equal to 150 (0.8 points to 120 points) is B, if 150 is less than 0.8, 150 is greater than or equal to 0.6, 119 (90 to 150) is C, and 0.6 (89 points and below) is D, which is equivalent to J.
There are several formulas that can be implemented. below, only the J3 formula is provided. You can drop down other formulas.
= Lookup (G3, 150 * {0, 0.6, 0.8, 0.9}, {"D", "C", "B", ""})
= Vlookup (G3, {0, "D"; 90, "C"; 120, "B"; 135, "a"}, 2)
= Hlookup (G3, {0, 90; 120,135; "D", "C", "B", "a"}, 2)
= Choose (MATCH (G3, 150 * {0, 0.6, 0.8, 0.9}), "D", "C", "B", "")
= Hlookup (G3, choose ({1; 2}, {120,135,}, {"D", "C", "B", "a"}), 2)
= If (G3> = 135, "excellent", if (G3> = 120, "good", if (G3> = 60, "medium", "poor ")))