Formula to calculate bonus - must cap the bonus within formula

[kari481]

Hi -

I'm looking for a formula to calculate a bonus.


The person's bonus is based on percent to quota. If they make 100% quota they make a $10,000 bonus. If they make 80% of quota, they make $8,000. 150% of quota - $15,000.

We need to cap this at 200% to quota or no greater than $20,000 bonus.

Anyone out there - can you assist - understand the logic with greater and less than but not sure how to incorporate the logic if they go over 200% and deducting that from the capped $20,000..

Thank you!

[FlameRetired]

Try this:

Row\Col	A	B	C	D	E
1	Quota	Made	%Pct	Bonus
2	$25,000.00	$20,000.00	0.8	$8,000.00	In C2: =B2/A2
3	$25,000.00	$25,000.00	1	$10,000.00	In D2: =MIN(C2*10000,20000)
4	$30,000.00	$45,000.00	1.5	$15,000.00
5	$20,000.00	$40,000.00	2	$20,000.00
6	$30,000.00	$75,000.00	2.5	$20,000.00

[kari481]

Thank you, Dave!!!

[FlameRetired]

You are welcome. Thank you for the rep.