Prev: How to convert 115:14 into hours? ( 115 stands for hrs, 14 is minu
Next: Populating Row with dates
From: Luke Androsiglio on 30 Mar 2010 21:51
I am trying to accomplish this in terms of letter grades corresponding
to number grades. How do I format a cell so that I can enter a letter
grade: A, A-, B+, B, etc. and the corresponding number: 4.00, 3.67,
3.33, 3.00, etc. to each letter grade will be the numeric value of the
cell (which I can reference in a formula in a different cell), while
the letter remains displayed in the cell?
As an example: in cell A1, I would type: B+, I would like B+ to remain
displayed, but the value of the cell would be 3.33. Therefore, I could
multiply cell B1 (which has a value of 3.00) to cell A1 and resulting
answer would be 9.99.

Thank you
From: T. Valko on 30 Mar 2010 22:37
How about creating a 2 column table that lists the letter grades in the left
column and the corresponding numeric value in the right column. Like this:

...........J..........K
1.......A..........4
2.......A-........3.67
3.......B+........3.33
4.......B..........3

Then:

A1 = some letter grade like B+
B1 = 3.00

C1 formula:

=B1*SUMIF(J1:J4,A1,K1:K4)

Result = 9.99

--
Biff
Microsoft Excel MVP


"Luke Androsiglio" <luke.androsiglio(a)gmail.com> wrote in message
news:881b5819-2465-47eb-aefd-a58ce5ac3fec(a)f13g2000vbl.googlegroups.com...
>I am trying to accomplish this in terms of letter grades corresponding
> to number grades. How do I format a cell so that I can enter a letter
> grade: A, A-, B+, B, etc. and the corresponding number: 4.00, 3.67,
> 3.33, 3.00, etc. to each letter grade will be the numeric value of the
> cell (which I can reference in a formula in a different cell), while
> the letter remains displayed in the cell?
> As an example: in cell A1, I would type: B+, I would like B+ to remain
> displayed, but the value of the cell would be 3.33. Therefore, I could
> multiply cell B1 (which has a value of 3.00) to cell A1 and resulting
> answer would be 9.99.
>
> Thank you


From: Gord Dibben on 31 Mar 2010 13:29
Example formula using a helper cell.

=IF(A1="","",LOOKUP(A1,{"a","a-","b+","b","b-","c+","c","c-","d+","d","d-","f"},{4,3.7,3.3,3,2.7,2.3,2,1.7,1.3,1,0.7,0})*B1)

entered in C1


Gord Dibben MS Excel MVP


On Tue, 30 Mar 2010 18:51:57 -0700 (PDT), Luke Androsiglio
<luke.androsiglio(a)gmail.com> wrote:

>I am trying to accomplish this in terms of letter grades corresponding
>to number grades. How do I format a cell so that I can enter a letter
>grade: A, A-, B+, B, etc. and the corresponding number: 4.00, 3.67,
>3.33, 3.00, etc. to each letter grade will be the numeric value of the
>cell (which I can reference in a formula in a different cell), while
>the letter remains displayed in the cell?
>As an example: in cell A1, I would type: B+, I would like B+ to remain
>displayed, but the value of the cell would be 3.33. Therefore, I could
>multiply cell B1 (which has a value of 3.00) to cell A1 and resulting
>answer would be 9.99.
>
>Thank you

 | 
Pages: 1
Prev: How to convert 115:14 into hours? ( 115 stands for hrs, 14 is minu
Next: Populating Row with dates