GPA或Grade Point Average是西方国家，尤其是^{(Grade Point Average)}美国^(USA)大部分大学用来判断学生平均成绩的重要参数。GPA可帮助公司在短时间内（通常为 0 到 5）了解学生的整体成绩。如果你想用Excel计算^(Excel)GPA，请通读这篇文章。

如何在Excel中计算GPA

GPA没有固定的比例，通常，它因大学而异。因此，我们将在Excel中创建一个比例表来确定参数，然后在示例中使用它。我们需要 3 个参数来找到GPA。它们是百分比、等级和等级值。

注意^(Note)：此过程适用于MS Excel for Windows版本，而不是 Web 版本。

让我们假设分级过程如下：

0% 至 39% = F 级或 0 值

40% 到 49% = D 级或 1 个值

50% 到 59% = C 级或 2 值

60% 到 79% = B 级或 3 值

80% 及以上 = A 级或 4 值

GPA的公式如下：

GPA = Sum of grade values in all subjects / Total number of subjects

GPA 的 Excel 公式变为：

=<cell with sum of grade values>/<total number of subjects>

通过一个例子可以更好地理解这一点。

例如。让我们假设以下示例。逐步找到GPA。

1]学生在各个科目中的得分百分比在A列和B列中，成绩在C列中需要提及，在D列中的成绩值如下图所示。

2]创建^(Create)一个具有固定值的第二个表，用于根据得分百分比来判断等级和值。该百分比应该是获得某个等级所需的最少百分比。为第二个表格创建^(Create)边框以将其与第一个表格区分开来。

3]单击^(Click)需要一年级值的单元格一次（双击将启用文本，所以不要这样做）。

4]转到Formulas > Lookup & Reference。

5]从功能列表中选择VLOOKUP 。这将打开函数^(Function)参数窗口。

6] 在Lookup_value字段中，键入第一个单元格的单元格坐标以及得分百分比。

7] 对于Table_Array，选择您将用于参考值的第二个表。然后，在每个列字母之前和之后添加 $。在我的例子中，表格数组的公式变为 $A$10:$C$15。

8] 在Col_index_num中，提及所选表中的列号。就我而言，由于我需要成绩并且成绩参考在第二列，因此Col_index_num变为 2。

9]点击确定。^{(Hit Ok.)}您将获得与所选单元格中的百分比相对应的等级（在我的情况下为 C3）。

10]使用^(Use)Fill函数将结果下拉到最后一行的百分比。为此，请单击单元格 C3 外部，然后再单击它。然后，使用所选单元格 C3 右下角的小点将公式下拉到单元格 C8。

11] 要查找值列表，请使用与等级相同的过程，不同之处在于Col_index_num为 3。

12] 使用AutoSum在单元格 D9 中查找所有成绩值的总和。

13]要在单元格H3中找到GPA，公式变为：

=D9/6

14]按回车键。

你有它！

如果您对程序有任何疑问，请在评论部分告诉我们。

希望这会有所帮助。

How to calculate Grade Point Average or GPA in Excel

GPA or Grade Point Average is an important parameter used to judge the average results of students in most universities in western countries, especially the USA. The GPA helps companies understand the overall result of a student over a short scale (usually 0 to 5). If you wish to calculate GPA in Excel, please read through this article.

How to calculate GPA in Excel

GPA doesn’t have a fixed scale and usually, it varies across universities. So, we will create a scale table in Excel to decide the parameters and then use it in an example. We will need 3 parameters to find the GPA. They are percentage, grade, and grade value.

Note: This procedure works with the MS Excel for Windows version and not the web version.

Let us assume the grade process as follows:

0%   to 39% = F grade or 0 value

40% to 49% = D grade or 1 value

50% to 59% = C grade or 2 value

60% to 79% = B grade or 3 value

80% and above = A grade or 4 value

The formula for GPA is as follows:

GPA = Sum of grade values in all subjects / Total number of subjects

The Excel formula for GPA becomes:

=<cell with sum of grade values>/<total number of subjects>

This could be better understood through an example.

Eg. Let us assume the following example. Proceed step by step to find the GPA.

1] The percentages scored by a student in various subjects are in columns A and B, the grades need to be mentioned in column C, and the grade values in column D as shown in the picture below.

2] Create a second table with fixed values for judging the grade and value as per percentage scored. The percentage should be the least needed to score a certain grade. Create borders for this second table to distinguish it from the first one.

3] Click on the cell in which you need the first-grade value once (double-click will enable text so don’t do that).

4] Go to Formulas > Lookup & Reference.

5] Select VLOOKUP from the list of functions. This would open the Function arguments window.

6] In the Lookup_value field, type the cell coordinates of the first cell with the percentage scored.

7] For Table_Array, select the second table which you would use for reference values. Then, add $ before and after each column letter. In my case, the formula for the table array becomes $A$10:$C$15.

8] In the Col_index_num, mention the column number in the selected table. In my case, since I need the grade and the grade reference is in the second column, the Col_index_num becomes 2.

9] Hit Ok. You would get the grade corresponding to the percentage in the selected cell (in my case C3).

10] Use the Fill function to pull the results down to the last row with the percentage. To do so, click outside cell C3 and then back on it. Then, use the little dot at the right-bottom corner of the selected cell C3 to pull the formula down to cell C8.

11] To find the list of values, use the same procedure you used for the grade, with the difference that the Col_index_num would be 3.

12] Use AutoSum to find the sum of all grade values in cell D9.

13] To find the GPA in cell H3, the formula becomes:

=D9/6

14] Hit Enter.

There you have it!

If you have any doubts about the procedure, please let us know in the comment section.

Hope this was helpful.

Related posts

• Exclamation Point Fix Yellow Triangle在Excel or Word

• Excel中的Calculate Simple，Weighted和Exponential Moving Average

• 如何在Excel使用MIN，Max和AVERAGE Functions

• 如何在Excel中使用Percentile.Exc function

• 如何使用NETWORKDAYS function在Excel

• 如何在一页上打印Excel or Google Sheets中所选单元格

• Microsoft Excel在Windows 10运行时导致High CPU usage

• 如何在Excel formula中锁定单元格以保护它们

• 如何更改默认File Format，用于保存Word，Excel，PowerPoint

• 如何在Excel中使用DCOUNT and DCOUNTA Function

• 如何计算在Excel的Yes or No entries数

• 如何在Excel中创建Organization Chart

• 如何在Excel中使用DEC2Bin将十进制数转换为二进制数

• 运行时error 1004，不能在Excel运行Macro

• 如何在Excel工作表中Freeze and Split Panes

• 如何在Excel中计算列中的重复值

• 如何在Excel中使用Automatic Data Type feature

• 如何在Excel中使用DISC function

• Excel，Word or PowerPoint上次无法启动

• 如何在Excel中使用DGET function