Combination of CountIF, IF, and NOT

**shani20** · 07-08-2011, 03:12 PM

I am helping someone at my work to do some formula on evaluation -

For example -

There are three evaluators on one subject. Each of them evaluate with those following letters - S, U, I.

Now we need to "average" those ratings - Such as -

S, S, S = S
S, S, I = S
S, I, I = I
U, S, I = I
I, I, I = I
I, I, U = I
I, U, U = U
U, U, U = U

Need to reevaluate =
S, S, U
S, U, U

So the first part is very easy = the formula goes -
=IF(COUNTIF(D10:I10,"S")>=2,"S",IF(COUNTIF(D10:I10,"I")>=2,"I",IF(COUNTIF(D10:I10,"U")>=2,"U")))

But when I tried to add NOT "U" in the first part (S, S, U) basically, if there is an U with two S, then that category needs to be reevaluate. I couldn't get that to work. As for U as well - if there are two Us, but with one S, then it needs to be re evaluate.

I'd appreciate if you are able to assist with this formula

It is the LAST one that we need to figure.

Shannon

**Ron Coderre** · 07-08-2011, 03:22 PM

It's really important to post the version of Excel you are using so we can tailor our responses.

If you have Excel 2007 or later...
try this regular formula to score values in A1:C1:

=MID("SIU",IFERROR(MODE(MATCH(A1:C1,{"S";"I";"U"},0)),2),1)

or this...

=INDEX({"S","I","U"},IFERROR(MODE(MATCH(A1:C1,{"S";"I";"U"},0)),2))

Note: that formula assumes that all 3 cells contain valid entries (S, I, or U)

Is that something you can work with?

**shani20** · 07-08-2011, 03:39 PM

I am working with MS Excel 2010.

I'm not worrying about valid letters - but to indicate if this category needs to be reevaluate

For example -

Evaluator #1 = S (satisfactory)
Evaluator #2 - I (Need improvement)
Evaluator #3 - U (Unsatisfactory)

Now -
One catergory - Turn=Taking Mechanism -

the results are -
S, S, U (this is invalid due to a huge difference between those two rates - the raters need to meet together and discuss why one feels that the testee did not do a good job in that area). So the results need to come out as "Need to Reevaluate"

If it is S, S, I, it will come out as S (which I accomplished)

The only two possibles that requires the results = "Need to Reevaluate" are:
S, S, U
S, U, U

In my formula, if there is 2 or more Us, the rate will be U. But not if it has S, if so, then it needs to be re evaluate.

I hope my question is clear? If you need the file, i will send it to you.

Shannon

**Ron Coderre** · 07-08-2011, 04:28 PM

I misinterpretted the "Need to re-evaluate" section.
Perhaps something like this regular formula could be used:

 
=INDEX({"U";"N";"N";"S";"U";"I";"S";"I";"I";"I"},
MATCH(COUNTIF(A1:C1,"I")&COUNTIF(A1:C1,"S")&COUNTIF(A1:C1,"U"),
{"003";"012";"021";"030";"102";"111";"120";"201";"210";"300"},0))

Where each digit indicates the count of the letters I, S, and U (respectively)

or...using this lookup range in Y1:Z11

 
ISU     Score
003     U
012     N
021     N
030     S
102     U
111     I
120     S
201     I
210     I
300     I

and this regular formula

 
=VLOOKUP(COUNTIF(A1:C1,"I")&COUNTIF(A1:C1,"S")&COUNTIF(A1:C1,"U"),$Y$2:$Z$11,2,0)

Is that something you can work with?

**shani20** · 07-08-2011, 06:58 PM

It works!!!! (the regular code). I need to understand how it works, but at least it works so I can give the file back to the person who are doing evaluations next week.

Shannon