Excel:使用XLOOKUP计算GPA

我正在制作一个Excel电子表格来计算平均成绩(GPA)。在A栏中,我有课程名称;在B栏中,相应的学分数;在C列中,字母等级。

Course I     4.00      A
Course II    4.00      A
Course III   4.00      B
Course IV    4.00      B

我在单独的工作表上有成绩系统,在A列中有字母成绩,在B列中有相应的成绩点。

A     4.00
A–    3.67
B+    3.33
B     3.00
B–    2.67
C+    2.33
C     2.00
D     1.00
F     0.00
S     —
AU    —
I     —
W     —
NGR   —

In the simplest case, I calculate GPA as the ratio of the sum of the products of the credits and grade points to the sum of the credits. In the example above, that would be (4.00*4.00+4.00*4.00+4.00*3.00+4.00*3.00)/(4.00+4.00+4.00+4.00). In Excel, I can make a column D that has the weighted grade points: =B1*XLOOKUP(C1,Grades,GradePoints), where Grades and GradePoints are A1:A9 and B1:B9, respectively, on Sheet 2. Each cell in column D takes the grade from column C, looks up the corresponding grade points, and multiplies by the number of credits. Then I can sum column D (=SUM(D1:D4)) and divide it by the number of credits (=SUM(B1:B4)), and voilà, we have a GPA.

但…

当我有一个S(令人满意),一个W(撤回),一个I(不完整)或其他没有分数的称号时,我遇到了问题。让我们用以下示例替换第一个示例:

Course I     4.00     A
Course II    0.50     S
Course III   4.00     A
Course IV    4.00     B
Course V     4.00     B

When I’m calculating the grade points, I want to exclude course II. Naturally, I would want to use SUMIF or SUMIFS, but I can’t figure out what the criteria would be to sum only the credits that have a corresponding grade in the named range Grades. If I have =ISNUMBER(XMATCH(C1,Grades)), I get TRUE or FALSE appropriately, but I don’t know how to use that in SUMIF(S). I have also tried using INDEX/MATCH without success. For the grade points in column D, I could have XLOOKUP return zero if the grade isn’t in the named range Grades, e.g., =B2*XLOOKUP(C2,Grades,GradePoints,0) would return zero since S is not in Grades. Finally, I don’t actually want to have an explicit column D or hidden cells either. I just want to show the GPA. Any help would be greatly appreciated!