if stament help

[djwbaker]

hi

I am still getting to grips with some formula so any help is much appreciated

I am trying to calculate a 1 point bonus when 5% is hit and a 2 point bonus when 10% is hit. rest of the weeks will be 0 as its a 1 time bonus

https://www.dropbox.com/s/o5wodrhc66p9yg9/bonus.jpg?dl=0

I currently have this but it returns criteria multiple times
I have used > 5%/10% as the weekly results might not be an exact match and could surpass

=IF(F3>=$C$2,2,IF(F3>=$B$2,1,0))

again any help much appreciated

Dan

 

[owstie]

I presume that is the formula you have in cell F6. Try this instead:

=IF(F3>=$B$2,IF(F3>=$C$2,IF(COUNTIF($E6:E$6,2)>0,IF(COUNTIF($E6:E$6,1)>0,0,1),2),IF(F3>=$B$2,IF(COUNTIF($E6:E$6,1)>0,0,1),0)),0)

 

[djwbaker]

brilliant thank you

 

[djwbaker]

HI,
this worked great till I changed my spread sheet
I have tried recreating it but just returns 0's

I have attached new layout if anyone can help?

columns EK and EL contain bonus targets
columns DQ to ED show lookup data
columns EP to FC is where I want bonus markers shown

https://www.dropbox.com/s/fskaffiffjj9eqp/BONUS1.png?dl=0

I tried this but as I said just zeros

 

[djwbaker]

=IF(DQ9>=$EK$9,IF(DQ9>=$EL$9,IF(COUNTIF($EM$9:EM9,2)>0,IF(COUNTIF($EM$9:EM9,1)>0,0,1),2),IF(DQ9>=$EK$9,IF(COUNTIF($EM$9:EM9,1)>0,0,1),0)),0)

this is my failed attempt