# Commission calculator

#### tonyna22

##### Board Regular
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...

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### gardnertoo

##### Well-known Member
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
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
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

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

#### tonyna22

##### Board Regular
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
Thanks

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,025
Messages
5,835,010
Members
430,332
Latest member
Charly_Moon

### 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.

### Which adblocker are you using?

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

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