Help: Mutiple conditions

[kiukeung]

Hi all,

I have a problem about calculate bonus of the following condition
which bother me for a long time. Can anyone help me?
What foumula should be used? Thanks

How to satify with Bonus scheme 2 (showsin the following), I have trie
to use AND(OR(),OR()), but it will give a repeated answer.

I think using If funtion seems reasonable, but my answer still can no
satify Bonus scheme 3.



Name Qtr1 Qtr2 Qtr3 Qtr4 Annual Total
_______________________________________
Mary 300 463 300 400 1000
John 430 530 510 503 2050
Tom 430 490 480 450 2050
______________________________________
Qtr
Target 400 500 500 500
_____________________________________

BONUS SCHEME:

1. if a salesperson achieve sales=> Taget figure in that quarter,
Bonus=0.5% *annaul total

2. If sales equal or exceed the target figure in 2 or more quarter,
Bonus =1% * annaul total

3. In addition, if the Annual toal >2000, the salesperson is awarded a
additional bonus 0.2% of Annual total

 

[Earl Kiosterud]

Kiukeung,

I'll presume Mary's QTR1 amount in B3:e3, and the target in B7:e7, and her
total in F3. Her bonus (Scheme 1 and 2) is given by:

=F3*SUMPRODUCT((B3:E3>=B7:E7)+0)/2/100

This works because the scheme 2 solution is exactly twice that of scheme 1.
A more generalized solution would be required if that weren't the case.

I didn't do the scheme 3 bonus. I need some coffee. Post back if you want
help with that.

 

[kiukeung]

Earl Kiosterud, thanks for your kindly help.
I attched this file in this reply, hope you can help me more.

Attachment filename: bonus.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=381888

 

[kiukeung]

I've got the answer, thanks again for helping.