Giving Text a Numerical Value

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

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)

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" <ryanklein@cinci.rr.com> wrote in message
news:utEa7dUTGHA.5828@TK2MSFTNGP14.phx.gbl...
> 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
>

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.
"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:1142989621.416930.171640@t31g2000cwb.googlegroups.com...
> 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)
>

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




"ryanklein" <ryanklein@cinci.rr.com> wrote in message
news:eUUlEOVTGHA.4452@TK2MSFTNGP12.phx.gbl...
> 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.
> "Harlan Grove" <hrlngrv@aol.com> wrote in message
> news:1142989621.416930.171640@t31g2000cwb.googlegroups.com...
>> 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)
>>
>
>