working out commission threshold formula - HELP!

mrcdave

New Member
Joined
Mar 23, 2013
Messages
2
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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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!
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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
Back
Top