Problems with SUM/AVERAGE for cell range using IF statement

joedavis123

New Member
Joined
Dec 27, 2005
Messages
8
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Book16
ABCDEFGHIJ
1Conversion Table
2O16O4
3EC3
4EE2
5CN1
6NU0
7O
8
9
10
Sheet1


C2:

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

joedavis123

New Member
Joined
Dec 27, 2005
Messages
8
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,811
Messages
5,574,454
Members
412,595
Latest member
slim313
Top