Formula to calculate bonus percentage that is not linear?

[fumann]

I'm having a brain fart. How do I write a formula that calculates the percentage of bonus payout based on the picture/table below?

1. If the quota sold is less than 96 widgets, there is no bonus
2. If you reach 96 widgets you immediately receive 50% of your bonus.
3. Prorate the bonus with a linear formula when the number of widgets are sold between 97 to 101
4. Receive 150% Bonus if widget sold is between 103 and 107
5. Receive 200% bonus if widget sold is greater than 107

Thanks in advance!

 

[Dave Patton]

#3 Is not clear. What bonus?
What version of Excel are you using?
You are more likely to receive a clear example if you post your information in a format that we can use; not a picture.
N.B. You can use the forum's tool named XL2BB to post an extract of your information.

 

[footoo]

=IF(A2<96,0,IF(A2<97,0.5,(IF(A2<102,(A2-96)*0.1+0.5,IF(A2<108,1.5,2)))))

Please note that sales of 102 widgets is missing from you post.

 

[Dave Patton]

You can review and test the following.
There are complex linear formulas that you could use.

l

T202304a.xlsm
	A	B	C	D	E
1
2	110	200%	200%	0	0%
3				96	50%
4				102	100%
5				103	150%
6				108	200%
7
5e

Cell Formulas
Range	Formula
B2	B2	=LOOKUP(A2,$D$1:$E$6)+AND(A2>96,A2<102)*(A2-96)/(102-96)*(1-0.5)
C2	C2	=LOOKUP(A2,{0,0;0,0;96,0.5;102,1;103,1.5;108,2})+AND(A2>96,A2<102)*(A2-96)/6*0.5