Excel takes care of the rest and recalculates all the letter grades for your students. The beauty of using one of the LOOKUP functions in this manner is that if you decide to change the grading scale, all you need to do is change the lower boundaries of each grade in the grading table. Now you can use a formula such as the following to return a letter grade: (How you name a range of cells is covered in other issues of ExcelTips.) When you are done putting in all five grade levels, you select the range (M3:N7) and give it a name, such as GradeTable. In M4 you place the lowest score for the next highest grade (53) and in N4 you place the corresponding letter grade (D). To its right, in cell N3, you place the letter grade for that score: F. In cell M3 you place the lowest possible score, which would be a zero. A different approach that is also more flexible involves defining a grading table and then using one of the LOOKUP functions (LOOKUP, HLOOKUP, and VLOOKUP) to determine the proper letter grade.Īs an example, let's assume that you set up a grading table in cells M3:N7. While such an approach will work just fine, using nested IF functions results in the need to change quite a few formulas if you change your grading scale. You could use the following formula to convert to a letter grade based on the scale shown above: For example, let's assume that a student's numeric grade is in cell G3. First of all, you could use nested IF functions within a cell. There are several ways that a problem such as this can be approached. For instance, you may have a grading scale defined where anything below 52 is an F, 52 to 63 is a D, 64 to 74 is a C, 75 to 84 is a B, and 85 to 99 is an A. If you do this, you may wonder how you can convert the numeric grade to a letter grade. Doing so is quite easy, as you sum the results of various student benchmarks (quizzes, tests, assignments, etc.) and then apply whatever calculation is necessary to arrive at a final numeric grade. Your browser can’t show this frame.Some teachers use Excel worksheets to calculate grades for students. If you’d like to see the steps in action, you can watch the short video below. This is the default setting, and if you omit TRUE, you’ll get the same result from the formula. The fourth argument, TRUE, means that an approximate match is okay.When a percentage match is found in the first column, a letter grade from the 2nd column will be returned. The third argument, 2, is the column number in the lookup table, where the Grades are located.The reference is absolute $B$4:$C$8, instead of relative, so that the VLOOKUP formula can be copied to other cells, and the table reference won’t change. The second argument, Grades!$B$4:$C$8, is the location of the lookup table.In the VLOOKUP formula, the first argument, B4, is the value that we want to look up in the Grades table.Copy the formula down to the remaining cells in the report card.Press the Enter key, and the grade for English - B - is returned.On the Report Card sheet, in cell B4, enter the percentage - 77.
The scores are entered on a sheet named Report Card, where a VLOOKUP formula calculates the grade. If not sorted A-Z, the results of the VLOOKUP formula might be wrong. Sort the percentages in Ascending order.In the second column, enter the matching Grade for each percentage.In the first column, enter the minimum percentage for each grade.In this example, the lookup table is created on a sheet named Grades. Based on the table below, all percentages of 85 or over should receive an A grade.
Instead, my lookup table should have the starting percentage for each grade.
For example, when working with student grades, I don’t want to create a lookup table where I have to enter every possible percentage. In some situations though, an approximate match is preferred, so several values will return the same result. I don’t want the name of a customer whose number is CLOSE to the one that I entered. For example, if I enter a customer number in one cell, I want the customer name in the adjacent cell. Usually when I use VLOOKUP, I want to pull information for an exact match.
0 kommentar(er)
