If the student name is A3: A762, there is a total of 150 points in G3: G762, and there are two points of division of four and other times:
First, the score is from high to low, the total number of people is one quarter, the second quarter is B, the third quarter is one C, the fourth quarter is D, filled with the H column at the alternative.
There are several formulas to be realized, and only the formula of the H3 is given below, and other pull-down.
= 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 ($ G $ 3: $ G $ 762, G3, 2), {0, 0.25, 0.5, 0.75; "D", "C", "B", "a"}, 2)
= IF (G3> Percentile ($ G $ 762, 0.75), "A", IF (G3> Percentile ($ G $ 3: $ G $ 762, "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))
Second, the fraction is greater than 0.9 (135 points and above) is A, less than 150 is 0.9 (120 minutes to 134 points) equal to 150 (120 minutes to 134 points) of B, less than 150 larger than or equal to 150 0.6 (90 points) By 119 points) is C, less than 150 0.6 (89 points and below) of D, and will be filled in the J column.
There are several formulas to be realized, and only the formulas of J3 are given below, and other pull-down.
= LookUp (G3, 150 * {0, 0.6, 0.8, 0.9}, {"D", "C", "B", "a"})
= 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", "A")
= HLOOKUP (G3, Choose ({1; 2}, {0, 90, 120, 135}, {"d", "c", "b", "a"}), 2)
= If (g3> = 135, "excellent", IF (g3> = 120, "good", IF (g3> = 60, "in", "difference"))))))
