Monday, July 12, 2010

How do you assign a letter grade to a number in an Excel spreadsheet?

A frequently asked question is how do you assign a letter grade to a numeric value.  This is actually very simple.  First, you need to create a defined name called "Grades" which refers to the array:

={0,"F";60,"D";70,"C";80,"B";90,"A"}


Next, use the VLOOKUP function to convert the number to the letter grade:

=VLOOKUP(A1,Grades,2)


In this case, A1 is the cell that contains the numeric value.  You can add entries to the Grades array for other grades like C- and C+, just make sure the numeric values in the array are in increasing order.


Also, I am currently planning for my honeymoon so what I have turned to in order to keep my notes and thoughts organized? Excel spreadsheets of course! So stay tuned for my Honeymoon/vacation planning/budgeting Excel spreadsheet.

5 comments:

  1. how to write the formula to assign a number to a value like 0 to 1.9 get "7" points, 2.0-3.9 gets "3" points, etc.

    ReplyDelete
    Replies
    1. Hi. Thanks for the questions. Change the defined name formula to: ={0,"7";2,"3";4,"2";6,"1"}

      Delete
  2. Thanks for the advice.Only issue is it will be a group of numbers that will be assigned a specific value. For instance 0 THROUGH 1.9 will be assigned "7" points and 2.0 THROUGH 3.0 will be assigned 5 points. Having difficulty writing "THROUGH" formula. Yikes.

    ReplyDelete
  3. I would like to know how, in my timesheet, I can have a hidden value of "1" to represent the "W" I have to insert in the spaces for the days of the month, so as i put in a W for the 1st, a W for the 2nd and so on they will automatically total up in the number of days worked column

    ReplyDelete
    Replies
    1. Insert a new column with the 1 value then hide the column.

      Delete

I'd love to hear from you!
-Nick