Excel 2007: Assigning Letter Grades Using IF Formula

February 2010

Problem

You wish to create a rule that assigns the appropriate letter grade to each student, based on the student’s total score in the class.

Solution

Create a rule based on “IF” logic that defines the range of scores for each letter grade. Use nested “IF” statements such that first a check if made to see if a score deserves an “A”. If it does, an “A” is inserted in the cell. If the rule fails, the next letter grade interval IF statement is tested, and so on.

Example

  1. Create a “Total” points column and a “Grade” column (the actual text labels are not important).

  2. Click in the first empty cell of the column (in example it is F2).

  3. Click inside the white entry box next to the function symbol, , type in an equals sign followed by a summation formula.

    • You can use a predefined function, such as “Sum(start cell:end cell)”, or you can type in a long hand equation, such as B2+C2+D2+E2

    • You can adjust your equation to show total points or a percentage score.

      Percentage = (student’s total score / maximum possible score)*100

  4. Verify that the sum is correct in the first “Total” cell.

  5. Highlight the cell for which you just defined the equation, place your mouse on the fat lower right corner of the highlighted cell border, then drag the fat corner down over the other student’s Total cells.

    • Dragging causes the formula to be applied to the cells you drag over, with the row number adjusted automatically to each row.

  6. Now click on the first empty “student” cell in the Grade column (in example, it is F3).

  7. Define a formula based on the breakpoints for each letter grade that follows the pattern below.

    • The IF statements are tested in the order in which they are written below. So the first score IS greater than or equal to 180 and is therefore given an “A”. The second student total score is 118, so every rule is false (not met) and the student gets the default grade of “F”.

    • Be careful to include all the quote marks and parenthesis. They should be in nested pairs (start…end).

  8. Highlight the cell containing the Grade equation , place your mouse on the fat lower right corner of the highlighted cell border, then drag the fat corner down over the other student’s Grade cells.

  • ODEET

    Office of Distance Education and Educational Technology