Calculating Commissions

  • Thread starter Thread starter Bugles6
  • Start date Start date
B

Bugles6

I am working with a friend who is trying to calculate commissions based on the
following information and we can't figure out how to create the formula or what
function we should be using.
example:
Gross Sales=665,000
Commission works like this:
If between 0-40,000 = 0
If between 40,000 - 150,000 = 30% commission
(150,000-40,000=110,000x.30=33,000)
If between 150,000 - 1,750,000 = 5% commission (665,000-150,000 =
515,000x.05=25,750)
His total commission will be 58,750.
Can someone help with this formula/function?
 
Bugles6 said:
Gross Sales=665,000
Click to expand...
Commission works like this:
If between 0-40,000 = 0
Click to expand...
If between 40,000 - 150,000 = 30% commission
(150,000-40,000=110,000x.30=33,000)
Click to expand...
If between 150,000 - 1,750,000 = 5% commission (665,000-150,000 =
515,000x.05=25,750)
Click to expand...
His total commission will be 58,750.
Click to expand...

Taking these commission rates as given,

=MAX(0,MIN(150000,GrossSales)-40000)*0.3
+MAX(0,MIN(1750000,GrossSales)-150000)*0.05
 
Another way courtesy of John McGimpsey

=SUMPRODUCT(--(A1>{40000;150000}),(A1-{40000;150000}),{0.3;-0.25})

where A1 holds the 665,000.00
 
Back
Top