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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

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,122,753
Messages
5,597,925
Members
414,191
Latest member
debbhatta

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top