Creating Commission Calculator Sheet

[Jryan582]

I am trying to create a sales commission calculator template that can be used for all of our employees. The commission structure is:

• 100% to Goal = $75 per admit
• 120% to Goal = $100 per admit
• 140% to Goal = $150 per admit
• 160% to Goal = $175 per admit
• 180% to Goal = $200 per admit

I need fillable cells to put "monthly goal" and "qualified admits" and then allow the formulas to take it from there. I am struggling because of the way the structure is based on % to goal rather than you hit x number and your payout equals y. If someone could help me with these formulas I would be forever grateful! I have browsed through the other threads and found questions that are close but not quite what I am looking for. The table I am currently working with looks like:

Thank you so much for your help!

 

[Dave Patton]

T202107a.xlsm
	A	B	C	D	E	F
1
2
3
4
5
6
7
8
9	Monthly Medicare Admission Goal	20
10	Medicare Admissions for the Month	22
11	LUPAs	1
12	Episodic Payer Admits **only if goal achieved, calculate at .5 per admit**	2
13	Total Admissions	23
14	Commission Payout	1725	1725
15					1	75
16					1.1	75
17					1.2	100
18					1.3	100
19					1.4	150
20					1.5	150
21					1.6	175
22					1.7	175
23					1.8	200
24
5d

Cell Formulas
Range	Formula
B13	B13	=SUM(B10,B12)-B11
B14	B14	=B13*LOOKUP(B13/B9,E15:F23)
C14	C14	=B13*LOOKUP(B13/B9,{1,75;1.2,100;1.4,150;1.6,175;1.8,200})