formula

**witt1989** · 07-29-2009, 09:02 AM

I am looking for a formula for the following

I have 20 therapists that I list deficiencies for. They can range anywhere from 0 to 25. I need to track week after week the following. 0 deficiencies would = 100 % compliance. 1-4 deficiencies would be partial compliance and 5 or greater would pose 0 % compliance. I have come up with a formula as follows for the first component ( 0 def. = 100%)=COUNTIF(J6:AL6,"=0")/COLUMNS(J6:AL6). However, I can't get a formula to work that would incorporate the other 2 scenarios. Any solutions?
Thanks,
Kim

**DonkeyOte** · 07-29-2009, 09:28 AM

Kim, were you not already provided with a solution for this ?

http://www.excelforum.com/excel-work...e-formula.html

ie:

=LOOKUP(SUM(J6:AL6),{0,1,5},{"100% Compliant","Partial Compliant","0% Compliant"})

**witt1989** · 07-30-2009, 11:48 AM

Yes, that formula does work. Now my coworker would like the partially compliant to actually be a percentage and not just be "partially compliant".
How would I change this to reflect a percentage instead of that statement?
Kim