Hello guys,

I've been tearing my hair out trying to figure out a solution and formula to this multiple condition problem:

SA HIRE DATE SALES QUOTA

BOB 1/1/2014 375,000 300,000
MIKE 1/1/2014 475,000 350,000
BILL 1/1/2014 275,000 150,000
SUE 2/1/2014 350,000 225,000
RODNEY 1/1/2014 350,000 225,000
NICK 1/1/2014 475,000 400,000
SARA 1/1/2014 275,000 200,000

Conditions:

1) $2k bonus for selling 50k above quota. Must sell a minimum of 300k
2) $4k bonus for selling 100k above quota. Must sell a minimum of 400k
3) Each rep is eligible to earn one bonus level
4) For mid quarter hires the bonus payout will be prorated based on number of months worked. The sales above quota requirement and the minimum sales requirement should be prorated as well.

How do you write the multiple condition formula to come up with a bonus answer in one column, is this eevn possible? Please help me!!!