How to Create a School Grades Formula in Excel

Microsoft Excel is a powerful spreadsheet program developed and maintained by Microsoft Corporation. Many mathematical, engineering, scientific, and financial problems can be resolved using built-in functions, custom functions, formulas, and Visual Basic for Application programs. In this article, a formula to calculate a school grade will be provided using IF, LEN, and VLOOKUP.

Difficulty: Moderate

Instructions

1. 
    
   image

   Open Microsoft Excel and enter the following formula into Worksheet cell B1:
   =IF(LEN(A1),VLOOKUP(A1,{0,"F";67,"D";70,"C-";73,"C";77,"C+";80,"B-";83,"B";87,"B+";90,"A-";94,"A";95,"A+"},2),"")

2. 
    
   image

   Beginning with cell B1, hold down the Shift key and use the down arrow to highlight a range of cells (Can also use left mouse to highlight range)and select Ctrl + D to Filldown the formula (Can also select from menu, Edit->Fill->Down).

3. 
    
   image

   Begin entering grade numbers starting from cell A1 (In our example grades from 66 through 100 are added). The grades should appear in column B for each corresponding entry in column A. If you do not see a grade, make sure there is a formula in the cell. Use F2 to verify that the formula in the current row references the current row (e.g. if the cursor is in cell B3, the formula should appear as =IF(LEN(A3),VLOOKUP(A3...

Tips & Warnings

• Ensure you fill the formulas down to the correct row
• You can change the formula to reflect a different grading scale by highlighting the formula cell and selecting F2 to edit
• Microsoft Excel 2007 uses a different menu system but the formula should work in that environment

[winnster]

on 6/23/2009 very nice tutorial. using excel to keep track of grades is actually very simple and effective.