Commission calculator

tonyna22

Board Regular
Joined
Apr 19, 2005
Messages
106
Okay here's what i need...this may be difficult to explain

I'm figuring out a new commission structure. if you hit 103% of plan you get $1000...any percentage above that goes as this...110% would equal = $1100. Then take the 110% minus the 103% which gives you 7%. Take 7% x 2 = 14%. Take 14% of $1000 and add to the original $1100. So the example of 110% would be...$1100 + 140 = $1240.

I know it's confusing...I'm trying to put this in excel...so everyone can automatically track their payout...can anyone help...?

Thanks...
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

gardnertoo

Well-known Member
Joined
Jul 24, 2007
Messages
938
How are you getting the first part of the bonus (the $1100 for a 110% achievement)? If 100% = $0, 103% = $1000, and 110% = $1100, what are the values for:
105%
125%
200%?
 

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
874
Hi tonyna22,

I'm not clear on the first step. You said, "any percentage above 103% goes as this...110% would equal = $1100."

So, just for the first step,
would 117% equal $1170 and would 125% equal $1250?

If so, assuming that the percentage of plan is in A1 and the commission you want to calculate is in B1, put this formula in B1:
=if(a1 < 103%, 0, a1*1000 + 2*(a1 - 103%)*1000)
 

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
874
Actually, I realize that the commission on 103% using this formula turns out to be $1030. That's not quite what you want but you'll have to look at the basis of the commission definition in order to make it completely consistent.
 

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
874

ADVERTISEMENT

The formula can be simplified to
=IF(A1 < 103%, 0, A1*3000 - 2060)
 

tonyna22

Board Regular
Joined
Apr 19, 2005
Messages
106
sorry basically there is no 100% it starts at 103%. The pool is $1000...so every % will be multiplied times $1000 to start then if you exceed the 103% the multiplier kicks in...

105% = $1050
125% = $1500
200% = $2000
 

tonyna22

Board Regular
Joined
Apr 19, 2005
Messages
106
Thanks

Dan Waterloo hooked me up...so thanks to Dan and everyone else...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,804
Messages
5,598,151
Members
414,214
Latest member
marketingnumbersguy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top