Excel question

I scoured the Internet and can’t find exactly what I’m looking for, so I’m holding my hat in my hand and asking for help.

I have a spreadsheet set up for each of my classes for assignments and grades. I have a key with a range of numbers that corresponds to a letter grade. What I’d like to do is add a column that automatically connects the numerical grade for each student with the key and inserts the letter grade automatically.

I’m thinking it will take some kind of if . . . then statements, but not sure where to start.

Thanks for any help you can give me.

Advertisements

7 thoughts on “Excel question

  1. Try the VLOOKUP function. If you need help from there and you don’t mind emailing me the spreadsheet then I’ll be happy to take a look at it.

  2. If you have a table like this, representing max grade percent and letter grade:
    Max Grade
    100 A
    90 B
    80 C
    70 D
    50 F

    Then you can use the formula:

    =INDIRECT(CONCATENATE(“B”,MATCH($J4,$A$2:$A$6,-1)+1))

    This formula assumes the grade table is in columns A and B. Also, the reference to $J4 is a single student’s grade. In my sheet, they were all in the same column (thus $J), and it just happened I copied the formula from the value for row 4.

  3. To make it work at the break points (70.0, 80.0, etc.), instead of using the actual number use something a miniscule fraction away… 69.99999, 79.999…). Otherwise, if the student’s grade falls directly on 70.0, for instance, it finds the next value down…

  4. Wow! Looking again at VLOOKUP, that works very nicely. Trick is sorting the numeric/grade key table in ascending order. That is what messed me up when I originally tested out VLOOKUP…

  5. I had that same problem George and kept getting an error message. Then I stopped and compared my table to the one in the example I found, and that was it. When I moved the table around and went back to my grade sheet – all the letters were in place. Woo Hoo!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s