searching for repeating text

[Guest]

Howdy~
I am using excel to complete weekly employee reviews. The employee rating
is done on a point scale. E.g., outstanding= 10, good = 8....etc

In the reveiw an employee gets a rating from the total point unless they
score needs improvment in two or a unsatisfactory in a single feild.

Is there a way to scan short list of text and give a points total unless the
above paragraph is met.

For example, the rating could have 4 fields and two employee scores
Outstanding = 10
Outstanding = 10
Outstanding = 10
Outstanding = 10
or a total of 40 points = outstanding
but a different employee could score

Outstanding = 10
Outstanding = 10
Outstanding = 10
Unsatisfactory= 4

How can I scan to find that single "U" to give a rating of Unsatisfactory?
I cannot use points becuase this empoyees high marks in the frist three
categories would skew the numbers

 

[Guest]

Hi Markh,

Have you tried using countif function? You could combine this with an IF
statement to indicate where there is a problem.

This formula will give you an unsatisfactory if the employees scores less
than 7 in more than one area of competence.
=IF(COUNTIF(B1:B5,"<7")>1,"unsatisfactory","")

Hope this helps.
Michael

 

[Guest]

Thanks~
is it possible to do the same/simular command testing for a feild with a
certain text in it?
thanks in advance

 

[Guest]

sorry for id-ten-t error on the last post. I can use the following
=IF(COUNTIF(C21:H21,"U")>1,"UNsat","")
but how can I add the more selection creteria
I need to check for two "U"'s and then for NI so i need to add the two
formula's together. adding the
=IF(COUNTIF(C21:H21,"NI")>2,"NI","") to intergetate the same cell.

thanks

 

[Guest]

Hi Mark,

This one should do it then, just nest the IF statements:
=IF(COUNTIF(C21:H21,"U")>1,"UNsat",IF(COUNTIF(C21:H21,"NI")>1,"NI",""))

Michael