Payout Calculation Based off Forecast Threshold Met

[campeao006]

Hi all,

I am trying to setup an automatic calculation based off a manually input percentage value. There are three different payment values, depending on how much is achieved.

The current payment is as follows.
For every percentage point up to 50%, I earn $1,000.
For every additional percentage point between 50% and 100%, I earn $2,000.
For every additional point beyond 100%, I earn $3,000.

So, if I only achieve 47% of the forecast, I earn $47, 000 (47 * $1000).
If I earn 60% of the forecast, I earn $70,000 (50 * $1000 + 10 * $2000).
If I earn 110% of the forecast, I thus earn $180,000. (50 * $1000 +50 * $2000 +10 * $3000).

Is there a way that I could input a percentage value in one cell and have the payout calculated automatically in the second?

 

[campeao006]

To elaborate on this, I am hoping that there is a way for cell B10 to be automatically calculated based off the number in cell B9.

 

[Dave Patton]

Try you preference

Commission2020.xlsm
	A	B	C	D	E	F	G
1
2			or	Arithmetic		Bracket	Rate
3	110%	180,000.00	180,000.00	180,000.00
4	91%	132,000.00	132,000.00	132,000.00		0%	1000
5	60%	70,000.00	70,000.00	70,000.00		50%	2000
6						100%	3000
7
4a

Cell Formulas
Range	Formula
B3:B5	B3	=SUMPRODUCT(--(A3>$F$4:$F$6),A3-$F$4:$F$6,$G$4:$G$6-$G$3:$G$5)*100
C3:C5	C3	=SUMPRODUCT(--(A3>{0;0.5;1}),A3-{0;0.5;1},{1000;1000;1000})*100
D3:D5	D3	=((A3>0)*A3+(A3>0.5)*(A3-0.5)+(A3>1)*(A3-1))*100*1000

 

[campeao006]

Thank you, appreciate it! This is a more robust version of what I figured out on my own using MIN and MAX functions.

 

[Dave Patton]

Thanks for the feedback.