Nested IF statements with specific conditions

[lesiahawk]

Hello all,

I am running into an issue with our bonus pay plan spreadsheet. Below are the conditions that have to be met in order for the agent to receive the bonus:

Leads and Assistant Leads will receive a bonus of $1.25 / hour if the following team and individual goals are met each week:

1. Call center averages 10% or less abandoned (missed) calls
2. CSR Agents as a whole, have an average talk time of 55% for the week
3. Lead and Assistant Lead individual goal of 33% talk time

OR

Leads and Assistant Leads will receive a bonus of $1.25 / hour if the following team and individual goals are met each week:

1. Call center averages 10.1 - 13% abandoned (missed) calls
2. CSR Agents as a whole, have an average talk time of 55% for the week
3. Lead and Assistant Lead individual goals talk time

My problem is coming in under column AA (the Yes,No column). Here is the formula that is currently being used and I can't figure out where I went wrong.

=IF($F$1<=13;IF(X14>=0.33;IF($I$1>=50;"Yes";"No");"No");IF(X14>=U14;"Yes";"No"))

I have also attached a copy of the spreadsheet as well. Based on the requirements, all of those YES's should be NO's. Any help would be greatly appreciated.

[etaf]

i may need it a little clearer

F1 = Abandoned rate
X? = Individual Average talk time - 33% for both
I1 = CSR talk time = 55%

so the only thing that changes is the
point 1

in rule one 10% or less
in rule 2 between 10.1 and 13

BUT the payments are the same

So basically its just 13% or less

in your formula I dont understand from the rules why you are using 50
IF($I$1>=50;"Yes";"No")
for CSR Talk Time


$F$1 <= 0.13 (13% CSR abandoned)
AND
$I$1 > = 0.55 (55% CSR talk time)

AND
individual
X? > = 0.33 = 33%

can we change =IF($F$1<=13;IF(X14>=0.33;IF($I$1>=50;"Yes";"No");"No");IF(X14>=U14;"Yes";"No"))
to
=IF( AND( $F$1 < = 0.13 , $I$1 > = 0.55 , X14 > = 0.33 ) , "YES", "NO)

edit, I'm in UK, so replace the , with a ;
=IF( AND( $F$1 < = 0.13 ; $I$1 > = 0.55 ; X14 > = 0.33 ) ; "YES" ; "NO)

X14 v U14 = target v indivdual
????? is that correct

X14 > =0.33 is this OR
X14 > = U14 (target)
?which to use ?

[lesiahawk]

I corrected the conditions. The lesser percent abandoned requires a lesser CSR Total Talk Time of 50%. The higher percent abandoned requires the higher talk time of 55%.

Correct, the payments are still the same however, the conditions are different. Now that I have adjusted the conditions, it should follow a lot smoother.

Leads and Assistant Leads will receive a bonus of $1.25 / hour if the following team and individual goals are met each week:

1. Call center averages 10% or less abandoned (missed) calls
2. CSR Agents as a whole, have an average talk time of 50% for the week
3. Lead and Assistant Lead individual goal of 33% talk time

OR

Leads and Assistant Leads will receive a bonus of $1.25 / hour if the following team and individual goals are met each week:

1. Call center averages 10.1 - 13% abandoned (missed) calls
2. CSR Agents as a whole, have an average talk time of 55% for the week
3. Lead and Assistant Lead individual goals talk time


F1 = Abandoned rate
X? = Individual Average talk time =
1. If the % abandoned is less than 10% AND the CSR Talk Time is 50% or higher THEN their individual average talk time is a set 33% or greater
2. If the % abandoned is between 10.1% and 13% AND the CSR Talk Time is 55% or higher THEN their individual average talk time is greater than or equal to the % TT Target (column U)
I1 = CSR talk time = This number can vary. At the moment we can't give it a set 55% because of the multiple conditions.

So with the breakdown, I need to be able to combine the following 2 formulas:

=IF( AND( $F$1 < = 13 , $I$1 > = 0.55 , X14 > = U14 ) , "YES", "NO)

=IF( AND( $F$1 < = 10 , $I$1 > = 0.50 , X14 > = 0.33 ) , "YES", "NO)



$F$1 <= 0.13 (13% CSR abandoned)
AND
$I$1 > = 0.55 (55% CSR talk time)

AND
individual
X? > = X?

=IF( AND( $F$1 < = 13 , $I$1 > = 0.55 , X14 > = U14 ) , "YES", "NO)

$F$1 <= 0.10 (10% CSR abandoned)
AND
$I$1 > = 0.50 (50% CSR talk time)

AND
individual
X? > = 0.33 = 33%

=IF( AND( $F$1 < = 0.10 , $I$1 > = 0.50 , X14 > = 0.33 ) , "YES", "NO)

[lesiahawk]

[QUOTE=etaf;4851890i may need it a little clearer[/QUOTE]

Let me know if the above reply helps.

Thanks!!

[etaf]

you can combine the two with an OR - BUT i Suspect its more complicated than THAT
=IF( AND( $F$1 < = 13 , $I$1 > = 0.55 , X14 > = U14 ) , "YES", "NO)
=IF( AND( $F$1 < = 10 , $I$1 > = 0.50 , X14 > = 0.33 ) , "YES", "NO)

=IF( OR ( AND( $F$1 < = 13 , $I$1 > = 0.55 , X14 > = U14 ), AND ( $F$1 < = 10 , $I$1 > = 0.50 , X14 > = 0.33 ) ) , "YES", "NO")

what takes precedence as a TEST
we could probably do a more complicated IF nested test
1st checking IF $I$1 > = 0.55

but maybe the OR works

[lesiahawk]

=IF( OR ( AND( $F$1 < = 13 , $I$1 > = 0.55 , X14 > = U14 ), AND ( $F$1 < = 10 , $I$1 > = 0.50 , X14 > = 0.33 ) ) , "YES", "NO)

Thanks. I tried with the combined statement and it worked in all scenarios except for scenario A and C below. Scenarios A and C are an automatic NO if the % Abandoned and the CSR TT fall into those categories.

% Aban CSR TT % TT Yes/No
A. <10 <49.9 automatic NO
B. <10 >=50 >=33 YES
C. 10.1-13 <54.9 automatic NO
D. <13 >=55 X?>U? YES

I'm not sure how to combine the formula to make all 4 scenarios work.

[etaf]

IS that

A
$F$1 <= 0.10 (CSR abandoned)
AND
$I$1 < 0.499 ( CSR talk time)
OR
C
$F$1 <= 0.13 , $F$1 >= 10.1 (CSR abandoned)
AND
$I$1 < 0.549 (CSR talk time)

we can setup

[lesiahawk]

IS that

A
$F$1 <= 0.10 (CSR abandoned)
AND
$I$1 < 0.499 ( CSR talk time)
OR
C
$F$1 <= 0.13 , $F$1 >= 10.1 (CSR abandoned)
AND
$I$1 < 0.549 (CSR talk time)

we can setup

Yes, absolutely

[etaf]

=IF( OR ( AND( $F$1 < = 13 , $I$1 > = 0.55 , X14 > = U14 ), AND ( $F$1 < = 10 , $I$1 > = 0.50 , X14 > = 0.33 ) ) , "YES", "NO")

we could add at the beginning of the formula

OR( AND ( $F$1 <= 0.10 , $I$1 < 0.499 ) , AND ( $F$1 <= 0.13 , $F$1 >= 10.1 , $I$1 < 0.549 ))

=IF(OR(AND($F$1<=0.1,$I$1<0.499),AND($F$1<=0.13,$F$1 >=10.1,$I$1< 0.549)),"NO",IF(OR(AND($F$1<= 13,$I$1 >= 0.55,X14>= U14), AND($F$1<= 10,$I$1>= 0.5,X14>= 0.33)), "YES", "NO"))

I dont like it , and sure there maybe a simplier formula where we dont have to have "NO" twice
BUT it works - so i can look at later in the week if i get a chance
or maybe someone else will provide a better solution

[lesiahawk]

=IF(OR(AND($F$1<=0.1,$I$1<0.499),AND($F$1<=0.13,$F$1 >=10.1,$I$1< 0.549)),"NO",IF(OR(AND($F$1<= 13,$I$1 >= 0.55,X14>= U14), AND($F$1<= 10,$I$1>= 0.5,X14>= 0.33)), "YES", "NO"))

Awesome! Thanks for all of your help etaf!

[etaf]

you are welcome