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.

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


"bollard" wrote in message
...
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.

Assuming data is in A1:D7:

=IF(COUNTIF($A$1:$D$7,"NOT")<=2,"ACHIEVE",IF(AND(C OUNTIF($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

"bollard" wrote:

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.

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.

"Toppers" wrote:

Assuming data is in A1:D7:

=IF(COUNTIF($A$1:$D$7,"NOT")<=2,"ACHIEVE",IF(AND(C OUNTIF($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

"bollard" wrote:

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.

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(CO UNTIF($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.

"bollard" wrote:

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.

"Toppers" wrote:

Assuming data is in A1:D7:

=IF(COUNTIF($A$1:$D$7,"NOT")<=2,"ACHIEVE",IF(AND(C OUNTIF($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

"bollard" wrote:

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.

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

"Toppers" wrote:

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(CO UNTIF($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.

"bollard" wrote:

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.

"Toppers" wrote:

Assuming data is in A1:D7:

=IF(COUNTIF($A$1:$D$7,"NOT")<=2,"ACHIEVE",IF(AND(C OUNTIF($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

"bollard" wrote:

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.