# working out commission threshold formula - HELP!

#### mrcdave

##### New Member
trying to forumlate a worksheet for monthly commission payments.

sold less than 60,000 so far in the year you get 3% commission paid that month
between 60,000-100,000 4.8%
over 100,000 7.5%

e.g
so if year to date (jan & feb) you had sold 70,000 and you sold another 20,000 in march you would get paid 4.8% of the 20,000 in march
if in jan & feb you had sold 55,000 year to date and you sold 20,000 in march you would get paid 5,000 at 3% and 15,000 at 4.8%

tried so many different ways of working it out and failed miserably, can someone help please!

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
thanks that really helped!

Onto the next problem though....

alongside the main sales there is a supplemetary bonus for a sales sideline.

This is paid up to 60,000 @ 2%
up to 100,000 @ 3.2%
over 100,000 @ 5%

HOWEVER the thresholds are still based on the main sales.
e.g
MAIN SALES:so if year to date (jan & feb) you had sold 70,000 and you sold another 20,000 in march you would get paid 4.8% of the 20,000 in march

SIDELINE SALES: year to date figures dont matter, if you did 5000 sideline sales in March you would get paid @ 3.2%(the second threshold) because youve hit the second threshold in the main sales threshold.
so the percentage is based on the main sales figures but paid as a percentage of the sideline sales figures.

HELP!

Replies
2
Views
406
Replies
3
Views
1K
Replies
25
Views
1K
Replies
6
Views
569
Replies
8
Views
278

1,196,203
Messages
6,013,991
Members
441,800
Latest member
CDra13

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