Converting letters to text and back again (Excel 2013)

Question

For a piece of coursework I have to complete a register of student grades. I am trying to calculate their overall grades by converting their grades from each of the four units into numbers (which I have done using the VLOOKUP function), but I need to then convert the result of the average back into a letter. I have used VLOOKUP and also a long nested IF statement to try and accomplish this, but no matter what I can never get a valid result. This is what I have so far (this is just a link to my image as I am unable to post one).

I have converted the Target grade into a number using the formula:

=VLOOKUP(D3,'Grade Values'!A$2:B$11,2,FALSE)

Then added up the total of the different grades from the four units using this formula:

=SUM(VLOOKUP(F3,'Grade Values'!A$1:B$11,2,FALSE)+
    VLOOKUP(Dashboard!G3,'Grade Values'!A$1:B$11,2,FALSE)+
    VLOOKUP(Dashboard!H3,'Grade Values'!A$1:B$11,2,FALSE)+
    VLOOKUP(Dashboard!I3,'Grade Values'!A$1:B$11,2,FALSE))

And then averaged it out with this: =J3/4

The problem I am facing at the moment is that when converting this number back to a letter using the same table as in the second screenshot I get an N/A result when I use this formula: =VLOOKUP(K3,Dashboard!A1:B10,1,FALSE)

I can't seem to figure out what's going wrong with the formula at the end. If anyone can please help me figure this out I will appreciate it a lot. Thank you :)

Edit: I apologise for the irrelevant tags, as far as I was aware formulas in Excel were written in JavaScript.

Answer 1

Personally, I would convert the letter grade to ASCII character using the function:

    CODE(A1)

use the ASCII Reference Chart for the integer value of each Upper Case character. Note: A=65, B=66, etc... Perform your calculations, then you can use the function:

    CHAR(A2)

to convert the number back into a character.

Example:
A1="A"
A2="B"
A3="C"

B1="=CHAR(AVERAGE(CODE(A1),CODE(A2),CODE(A3)))"

Answer 2

just copy the column A in the sheet dashboard and paste it in column C in the sheet dashboard... so you have

A        B         C
A*       7         A*
A        7         A
B        6         B

.... the formula you have to use is

=VLOOKUP(K3,Dashboard!$B$1:$C$10,2,FALSE)

remember that this time u need to match a number... not a letter