IF Formula Question

**lilsnoop** · 05-02-2007, 10:07 AM

I have created a non-subjective job evaluation spreadsheet, which takes data obtained from a work daily of an individual and is implemented in my spreadsheet. The columns of areas to be evaluated will average each individuals performance by averaging the individual to the rest of his shifts performance and a point system has been established depending on their work level. The situation I'm running into is I'm using the formula below to establish my point system. In an effort to be fair in every way possible, should a particular column end up with all zeros "0" this would then become the average, which should provide the individuals in this group with three "3" points. My forumula as it is set up will now only give one "1" point, when it should be three. The point system works great other than this one particular problem, when everyone has a zero. I'd sure appreciate anyone's suggestion how I might be able to add one more IF statement to this existing formula. Note: I29 is the cell for this particular column that represents the average of the column with data in it. I28 is 10% above average, I27 is 20% above average, I30 is 10% below average and I31 is 20% below average.
point system is 1 point for 20% below average, 2 points for 10% below average, 3 points for average, 4 points for 10% above average and 5 points for 20% above average.

=IF(ISTEXT(I3),0,IF(I3="",0,IF(I3<=$I$31,1,IF(I3<=$I$30,2,IF(I3<=$I$29,3,IF(I3<$I$28,3,IF(I3>=$I$27,5,IF(I3<$I$27,4))))))))

Thanks in advance!

**oldchippy** · 05-02-2007, 11:01 AM

Could be a problem - you already have 7 IF's. It would be better if you could post a sample of your worksheet

To post a zipped example, go to “My Documents” or wherever you have your file stored, right-click and “Send to > Compressed file”, then attach this zipped file to your post.

**lilsnoop** · 05-02-2007, 11:15 AM

Using the formula from my first inquiry i've copied those columns/cells involved into this sample sheet; i.e. columns I & J. This spreadsheet was made in excel 2007, but I saved in for earlier versions of excel.. So I did loose some conditional formatting factors, but it should affect what I'm trying to accomplish.. Thanks again for your time & efforts to help me with this issue!

**oldchippy** · 05-02-2007, 11:22 AM

Does this work? Put this in cell I29

=IF(SUBTOTAL(1,I3:I24)=0,3,SUBTOTAL(1,I3:I24))

**lilsnoop** · 05-02-2007, 11:33 AM

I tried it, but it only changed my average to three and didn't affect anything in column "J", which is where my point system is derived from. I was hopeful though!

**lilsnoop** · 05-02-2007, 09:14 PM

For those that might run into something similar to this...
I needed to add another If statement like this....IF(AVERAGE(I3:I24)=0,3, it worked when I inserted into my existing formula:

=IF(ISTEXT(I3),0,IF(I3="",0,IF(AVERAGE(I3:I24)=0,3,IF(I3<=$I$31,1,IF(I3<= $I$30,2,IF(I3<=$I$29,3,IF(I3<$I$28,3,IF(I3>=$I$27, 5,IF(I3<$I$27,4)))))))))

I appreciate the other suggestions as it made me think & think.....