Giving Text a Numerical Value

[ryanklein]

Is it possible to give a Text value a Numerical value. I am trying to set up
a grade calculator. I set it up so I can plug in the values of each letter
grade like 4.0, 3.7 and so on. How could I make it so that instead of having
the 4.0 there I could input A and it would still do the correct calculation.
I tried using LOOKUP and stuff but that still just returned the numerical
value. Thanks

 

[Harlan Grove]

ryanklein wrote...

Is it possible to give a Text value a Numerical value. I am trying to set up
a grade calculator. I set it up so I can plug in the values of each letter
grade like 4.0, 3.7 and so on. How could I make it so that instead of having
the 4.0 there I could input A and it would still do the correct calculation.
I tried using LOOKUP and stuff but that still just returned the numerical
value. Thanks

Setup: student names or IDs down the left hand column starting in row
2, assignment IDs in the top row starting in column B, letter grades in
the cells bounded by the column of student names/IDs on the left and
assignments on the top. Also, a table of grade to letter
correspondence, e.g., in a range named GradePoints in another worksheet

0.0 F
1.0 D
1.7 C-
2.0 C
2.3 C+
2.7 B-
3.0 B
3.3 B+
3.7 A-
4.0 A

If your grade table looked like

as't1 as't2 as't3 as't4
01 A A- F C
02 A C C+ A-
03 B+ B+ B- A
04 A- A A B+
05 D C- A- C+
06 B+ C- B+ B

Then you could calculate the grade for student 01 (in row 2) using the
formula

F2:
=LOOKUP(SUMPRODUCT(SUMIF(INDEX(GradePoints,0,2),B2:E2,
INDEX(GradePoints,0,1)))/COUNTA(B2:E2),GradePoints)

 

[ryanklein]

That would work but I just want to assign values like A = 4.0. I'm not doing
an entire grade book I'm just doing my class grades for each quarter to
figure out my GPA.

 

[ryanklein]

That would work but I just want to assign values like A = 4.0. I'm not doing
an entire grade book I'm just doing my class grades for each quarter to
figure out my GPA.

 

[Peo Sjoblom]

I think you will find that Harlan's formula does what you want, another way
using a one column range
with letter grades let's say A1:A20 then using the same grade points as
Harlan you can use

=SUMPRODUCT(COUNTIF(A1:A20,{"F";"D";"C-";"C";"C+";"B-";"B";"B+";"A-";"A"}),{0;1;1.7;2;2.3;2.7;3;3.3;3.7;4})

you can see that each letter has its value, just change to whatever you want

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon