Grading function in Excel

[Shreekant Patel]

Hello,

I hope that someone would be able to help me on what I am trying to
achieve. What I am trying to have is a simple grading system that
converts a grade to a number so for example:

A = 5
B = 4
C = 3
D = 2
E = 1

-What I have tried: (Works for one value)
In cell B1 I inputted a function using an IF statement where by the
Logical Test is A1="A" then The_Value_True = 5.

So the function is =IF(A1="A",5).

So if in Cell A1 I typed A, then in Cell B1, I get the number 5.

What I would like to have is, if Cells A1:A6 have the data, A, B, C,
D, A, D, I would like the numbers 5, 4, 3, 2, 5, 2 appear in the cells
B1:B6.

Your help is much appreciated on this little task.

Thanks
SP

 

[L. Howard Kittle]

Try this in the destination cells.

=LOOKUP(A1,{"a","b","c","d","e"},{5,4,3,2,1})

HTH
Regards,
Howard

 

[Guest]

=70-CODE(UPPER(A1))

and copy down

 

[Guest]

Another way

=IF(A1="a",5,IF(A1="B",4,IF(A1="C",3,IF(A1="d",2,IF(A1="E",1)))))

or to find the total in one go use:

=COUNTIF(A1:A6,"A")*5+COUNTIF($A$1:$A$6,"B")*4+COUNTIF(A1:A6,"C")*3+COUNTIF(A1:A6,"D")*2+COUNTIF(A1:A6,"E2")

Regards
Peter

 

[Gord Dibben]

Enter this in B1 and copy down as far as you wish.

=LOOKUP(A1,{"A","B","C","D","E"},{5,4,3,2,1})

Start entering letters in column A


Gord Dibben MS Excel MVP

 

[Guest]

Topper: I think its a little clearer to do the following

=Code("F")-CODE(UPPER(A1))

Most people would not know the ascii code for F was 70.

 

[Ron Rosenfeld]

Hello,

I hope that someone would be able to help me on what I am trying to
achieve. What I am trying to have is a simple grading system that
converts a grade to a number so for example:

A = 5
B = 4
C = 3
D = 2
E = 1

-What I have tried: (Works for one value)
In cell B1 I inputted a function using an IF statement where by the
Logical Test is A1="A" then The_Value_True = 5.

So the function is =IF(A1="A",5).

So if in Cell A1 I typed A, then in Cell B1, I get the number 5.

What I would like to have is, if Cells A1:A6 have the data, A, B, C,
D, A, D, I would like the numbers 5, 4, 3, 2, 5, 2 appear in the cells
B1:B6.

Your help is much appreciated on this little task.

Thanks
SP

Since you are getting all kinds of answers, here's another solution:

=IF(ISTEXT(A1),MATCH(A1,{"e","d","c","b","a"},0),"")


--ron

 

[Santipong]

Another way:

=SUM((A1<{"a","b","c","d","e"})+0)+1

Ctrl+Shift+Enter or

=SUMPRODUCT((A1<{"a","b","c","d","e"})+0)+1

Enter

Santipong