Problems with SUM/AVERAGE for cell range using IF statement

[joedavis123]

I am not well versed in using Excel formulas, but I am trying to put together a spreadsheet that is used to track employee performance. The grading scale that I am using is Outstanding, Commendable, Effective, Needs Improvement, and Unsatisfactory. The abbreviations (and subsequent scores) respectively are:

O = 4
C = 3
E = 2
N = 1
U = 0

I have two columns setup, the first one is where one of the above listed abbreviations is entered (O, C, E, N, U). The second column translates this into a score (4, 3, 2, 1, 0). I originally tried to have this value translated using a table, but had trouble getting this to translate every value (only O, E, and U would translate correctly). So I decided to use an IF statement instead on the second column:

=IF(A1="O","4","")&IF(A1="C","3","")&IF(A1="E","2","")&IF(A1="N","1","")&IF(A1="U","0","")

I know this might not be the proper way to do what I need, but it seems to translate properly. The problem I am encountering now is that SUM and AVERAGE statements at the bottom of the second column which calculates the numeric scores do not display anything other than a zero. If I enter the numeric values in manually into the second column, it works fine, so I think there is some problem with it reading the formula instead of the values it outputs. This might have to do with the way I am trying to implement the solution, so I am trying to find out if anyone can give me any pointers as to why this is happening, a way to fix it, or possibly a more efficient way to get these values to translate from letters to numbers.

Any help is greatly appreciated. Thanks

 

[fairwinds]

Welcome to the board!

Take away the double quotes around numbers. Just 4 not "4".

Or try:

=MATCH(A1,{"U","N","E","C","O"},0)-1

 

[Aladin Akyurek]

Book16
	A	B	C	D	E	F	G	H	I	J
1									Conversion Table
2	O		16						O	4
3	E								C	3
4	E								E	2
5	C								N	1
6	N								U	0
7	O
8
9
10
Sheet1

C2:

=SUMPRODUCT(SUMIF($I$2:$I$6,$A$2:$A$7,$J$2:$J$6))

 

[joedavis123]

Tried taking out the quotes but I had the same problem. Instead I used Fairwind's suggestion:

=MATCH(A1,{"U","N","E","C","O"},0)-1

It all works great now. I was getting the #N/A error on every cell, but used =IF(ISERROR and it works perfectly.

Thank you everyone for your help and quick responses.