Assigning values to letters - I know it's been asked before...

[Guest]

but the responses have been over my head. I've tried them, but obviously haven't executed them correctly. Here's what I'm trying to do:

create a spreadsheet with my students names and grades in all subjects that will calculate GPA>
The first column has student names, the next few have the subjects at the top (English, History, Etc.), and the final column is GPA. The grades are entered for each student in the same row as their name and under the appropriate subject. My problem is assigning numeric values to the letter grades (A,A-,B+,B,B,B-, C+,C,C-,D+,D,D-,F). I would greatly appreciate it if someone could explain to me, in layman's terms, how to do this.

Thanks,
Matthew

 

[Frank Kabel]

Hi
one way (if A1 stores your letter grade)
=CHOOSE(MATCH(A1,{"A","A-","B+","B","B-","C+","C","C-","D+","D","D-","F
"},0),1,2,3,4,5,6,7,8,9,10,11,12)
Adapt the numbers 1-12 to your needs

 

[Gord Dibben]

Matthew

Couple of suggestions.........

From Peo Sjoblom....
=IF(A1="","",VLOOKUP(A1,{0,"F";0.6,"D";0.7,"C";0.8,"B";0.9,"A"},2))

From Jason Morin......
=LOOKUP(A1*100,{0,60,70,80,90},{"F","D","C","B","A"})

NOTE: the use of the {}curly braces internally.

Gord Dibben Excel MVP

 

[Norman Harker]

Hi Matthew!

in L1:M12 I have the following table:

A 95
A- 90
B+ 85
B 80
B- 75
C+ 70
C 65
C- 60
D+ 55
D 50
D- 45
F 40


Now I can use the following formula:

=VLOOKUP(A1,$L$1:$M$12,2)

This formula looks up the entry in A1 in the table in L1:M12 and if it
finds the grade it reports the entry in the 2nd column of the table
that is against that grade.

Substitute A1 by the cell address of the first student's grade and
copy down. Because I've used relative referencing the student grade
address will change but it will still refer to the absolutely
referenced grade table.

If it doesn't find an entry it will return #N/A! This may be annoying
where you have blank cells awaiting grade inputs and you can avoid
this using:

=IF(ISNA(VLOOKUP(A2,$L$1:$M$12,2)),"",VLOOKUP(A2,$L$1:$M$12,2))

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

but the responses have been over my head. I've tried them, but

obviously haven't executed them correctly. Here's what I'm trying to
do:

create a spreadsheet with my students names and grades in all

subjects that will calculate GPA>

The first column has student names, the next few have the subjects

at the top (English, History, Etc.), and the final column is GPA. The
grades are entered for each student in the same row as their name and
under the appropriate subject. My problem is assigning numeric values
to the letter grades (A,A-,B+,B,B,B-, C+,C,C-,D+,D,D-,F). I would
greatly appreciate it if someone could explain to me, in layman's
terms, how to do this.

 

[Tom Ogilvy]

What numeric value do you want to assign for each of the letter grades?

--
Regards,
Tom Ogilvy

but the responses have been over my head. I've tried them, but obviously

haven't executed them correctly. Here's what I'm trying to do:

create a spreadsheet with my students names and grades in all subjects that will calculate GPA>
The first column has student names, the next few have the subjects at the

top (English, History, Etc.), and the final column is GPA. The grades are
entered for each student in the same row as their name and under the
appropriate subject. My problem is assigning numeric values to the letter
grades (A,A-,B+,B,B,B-, C+,C,C-,D+,D,D-,F). I would greatly appreciate it
if someone could explain to me, in layman's terms, how to do this.

 

[Arvi Laanemets]

Hi

=VLOOKUP("C",{"A","A-","B+","B","B-","C+","C","C-","D+","D","D-","F"},1,FALS
E)
or (it's depends what you have for list separator in your regional settings)
=VLOOKUP("C",{"A";"A-";"B+";"B";"B-";"C+";"C";"C-";"D+";"D";"D-";"F"},1,FALS
E)
will return 7 ("C" is seventh character in array)


--
(Don't use my reply address - it's spam-trap)

Arvi Laanemets

but the responses have been over my head. I've tried them, but obviously

haven't executed them correctly. Here's what I'm trying to do:

create a spreadsheet with my students names and grades in all subjects that will calculate GPA>
The first column has student names, the next few have the subjects at the

top (English, History, Etc.), and the final column is GPA. The grades are
entered for each student in the same row as their name and under the
appropriate subject. My problem is assigning numeric values to the letter
grades (A,A-,B+,B,B,B-, C+,C,C-,D+,D,D-,F). I would greatly appreciate it
if someone could explain to me, in layman's terms, how to do this.

 

[Tom Ogilvy]

I had to change it to Hlookup as you have written it, and then it returns C

=HLOOKUP("C",{"A","A-","B+","B","B-","C+","C","C-","D+","D","D-","F"},1,FALS
E)

Maybe you meant Match:

=MATCH("C",{"A","A-","B+","B","B-","C+","C","C-","D+","D","D-","F"},0)

 

[Arvi Laanemets]

Hi

It looks like it was too late for me to think somewhat coherently - almost 3
hours after midnight.

Of course the formula must be MATCH() !