If the thread title seems a little opaque, things should be clearer after i explain.
First, let me say that i’m no Excel expert. I barely ever use it. But one thing i do use it for is to make a spreadsheet of my students’ grades. I have the students’ names in one column, then i assign a column for each assignment, and a final column that takes all the assignments and calculates a final grade.
I grade using the letter system (A, B, C, etc.), and when i put the students’ grades into the spreadsheet i do it using the GPA equivalent. So, if the student got an A, i put in 4; if they got a B, i put in 3, etc.
What i was wondering is if there’s a way to assign each letter a numerical value, so i can just input the letter and have Excel automatically assign a numerical value to it for calculation purposes.
My idea is that there would be somewhere i could type something like…
Let A = 4
Let A- = 3.7
Let B+ = 3.3, etc., etc.
…and have the program use that guide when assigning values to letter grades.
Is this even possible, or do i have to stick with inputting numbers?
The best way I know to go about this is using the LOOKUP function. Basically, you have two columns: the first contains the values you want to look up (letter grades in this case) and the second contains the values you want to return (GPA here). You pass in a value from the first column, and LOOKUP returns the corresponding value from the second.
Lookup/VLookup are your answer here I think, though you could go with the previously mentioned IF statement route.
You could use a lookup table like this:
A B
1 A 4
2 A- 3.7
3 B+ 3.3
4 B 3
Then, lets say you have a grade of ‘B+’ in cell D24,
=VLOOKUP(D24,A1:B4,2,FALSE) would give you 3.3
Incorporating this properly into an averaging function is something for you to figure out.
Arnold, thanks for the advice. I looked up your formula, and you were on the right track, although your syntax was a bit off. It should read:
=IF(A1=“A”,4,IF(A1=“A-”,3.5,3))
with commas instead of semicolons. Still, it was very helpful and taught me something i didn’t know before.
The main problem with your solution, however, was that there seems to be a limit on the number of nested equations that Excel will allow. I need 12 separate values, from A down to D, then F, but when i plugged all these values into your formula, it returned an error.
Reducing the number of variables from 12 to 8 made the formula work fine, but then i was missing one third of my values.
So i looked up LOOKUP (ha ha), as suggested by ultrafilter, and found out the appropriate syntax, and it works perfectly.
Many thanks to both of you. I’ve learned a lot today. I could probably have found it all in Excel’s Help file after a lot of searching, but your advice meant that i knew what to look for, and made life much easier.
ETA:
Thanks to you, too, ArmenE. That’s exactly what i did.
OK, sorry, it’s not working, and i’m not getting how it works.
I get the general idea behind LOOKUP, but it seems to me that you need to create a separate LOOKUP function for every entry, which sort of defeats the time-saving aspect of what i’m trying to do here.
What i want to be able to do is to simply type, for example, A- into one column, and have that convert to 3.7 in another. And to repeat this step (with varying grades) for all 20+ rows in those columns.
I tried to do a LOOKUP array also, and it worked fine for grades A,B,C, and D. But as soon as i added B+ to the mix, it didn’t work anymore.
Dunno what the hell is going on. Any advice most appreciated.
This looks at the first character only in cell A1 and converts it to uppercase, so you can enter lowercase letters and still get the same result, plus any extraneous characters in the field would be ignored. It then reads the ASCII value of the letter and substracts it from 69. A = 65 in ASCII, so the result 69-65 gives you the result of 4; a B grade would be 69-66 (3), and so on. This doesn’t take into account pluses and minuses, so maybe someone can build upon my model to allow for this.