Rating according to 2 criteria.

B

bollard

Hello

On a spreadsheet at work, used to silent monitor incoming phone calls and
the way they are handled, we look our for 4 characteristics. Under each of
these headings are, at present, 7 items we look out for.

Everyone starts with 100% score for each characteristic and loses a point,
i.e. one seventh, for each of the 7 things they do NOT display.

To reach an overall rating, we want to allocate an "Exceed" to anyone who,
at worst, scores no more than 2 NOTs across all 4 characteristics.

The next rating down is "Achieve". To get this score, the operator may
score, at worst, 1 NOT in each characteristic.

Anyone who scores lower than this is a "NOT ACHIEVE".

Can anyone come up with a formula which will accomplish this, please?

Many thanks.
 
B

Bernie Deitrick

Bollard,

Your problem statement is confusing. But here is a formula that will give a return based on the
number of Nots entered into 4 cells: change the No Nots, One Not, etc to whatever you want.

=CHOOSE(COUNTIF(A2:D2,"Not")+1,"No Nots","One Not","Two Nots","Three Nots","Four Nots")

HTH,
Bernie
MS Excel MVP
 
T

Toppers

Assuming data is in A1:D7:

=IF(COUNTIF($A$1:$D$7,"NOT")<=2,"ACHIEVE",IF(AND(COUNTIF($A$1:$A$7,"NOT")<=1,COUNTIF($B$1:$B$7,"NOT")<=1,COUNTIF($C$1:$C$7,"NOT")<=1,COUNTIF($D$1:$D$7,"NOT")<=1),"ACHIEVE","NOT ACHIEVE"))

HTH
 
B

bollard

Hi Toppers.

I've placed the data in another sheet using the A1:D7 array you suggest. BUT
there appears to be an error concerning the number of brackets. Could you
take another look and see if you can identify where the brackets go wrong,
please?

Many thanks.
 
T

Toppers

Works OK with me BUT I had a typo: first ACHIEVE should be EXCEED

=IF(COUNTIF($A$1:$D$7,"NOT")<=2,"EXCEED",IF(AND(COUNTIF($A$1:$A$7,"NOT")<=1,COUNTIF($B$1:$B$7,"NOT")<=1,COUNTIF($C$1:$C$7,"NOT")<=1,COUNTIF($D$1:$D$7,"NOT")<=1),"ACHIEVE","NOT ACHIEVE"))

If you copy from here, be careful of line wrap.
 
B

bollard

Hi Toppers

That seems to ahve solved the problem.

Many thanks again.

i get to trial the form today, so we'll see whtehr the idea works in practice.

Keith
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top