Invoking madbard's LJ Genie (for some reason I almost called it

[kitchen_kink]

In Excel, how do you make it so that a given number will display as a given letter, and so that if you enter that letter in the cell, it will understand it as that letter's numerical value?

That is, if I want 3.67 to mean A-, how do I make Excel understand that?

Thankye!

Comments

You came up on friendsfriends

[mizdarkgirl.livejournal.com]

You need to use vlookup. Increase the values from my example to the actual range you want to use. Once you use this formula, cut and paste special, values only. If you need a more robust example I can set up a spreadsheet and send it to you.

=VLOOKUP(cell with grade,A1:B5,2)

a b
1 0 F
2 1 D
3 2 C
4 3 B
5 4 A

[moominmolly.livejournal.com]

The LJ Genie strikes again!

Re: You came up on friendsfriends

[dietrich.livejournal.com]

Thanks...but this makes no sense to me. :(

Excel says this function makes it search for a value in the leftmost column of the table...rar. I don't understand it or how to set it up.

It's not that important, though, Just would give me kicks if I could enter the letter 'A' instead of entering 4, and have it still do all the calculations in my formula and then display it as a letter grade.

Re: You came up on friendsfriends

[mizdarkgirl.livejournal.com]

Try when you are not in a grade crunch time...
1) Create a new workbook
2) Save it as grades.xls in the same directory as your other spreadsheet
3) Rename sheet1 as grades
4) Go to A1
5) Enter a 0
6) Hold shift key and page down until you are in a400
7) Edit, Fill Series, Step Value .01
8)Go to B1, Type F
9) While in B1, Hold shift key and page down until the value must change to D-
10) Continue until you have added all the letter grades in and hit file save
11)Go to your second spreadsheet with the actual grades
12) Go to the cell next to your grade (lets say 3.67 is in H2)
13) Put in this formula =VLOOKUP(H2,[grades.xls]Grades!$A$1:$B$400,2)
14) Your grade will appear

In this example:
=VLOOKUP(H2,[grades.xls]Grades!$A$1:$B$400,2)
=VLOOKUP(Lookup_valueTable_array,Col_index_num)

1)Lookup_value is the number you want to lookup; 3.67 in H2
2) Table_array is where the table is located; spreadsheet grades, sheet grades and the cells from a1 to b400
3) Col_index_num is the column you want to return a value from; in this case is the second hence 2

---------------
You can reuse the grades workbook once you have created it. There is an easier way to do set it up but this is the easiest way to explain it in this forum

I don't know you but...

[yagagriswold.livejournal.com]

*swoon!*

[fanw.livejournal.com]

You can always use nested if/then statements. If > 4 -> A, if >3 ->B, etc.

[mzrowan.livejournal.com]

You can define a constant that points to a value in a cell, so that when you type "=A", it inserts "4". But it doesn't display the constant instead of the number. I don't think there's a way to do that.

Search for "constant" in the help for more info.