Calculating Commissions

[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?

 

[Harlan Grove]

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.

Taking these commission rates as given,

=MAX(0,MIN(150000,GrossSales)-40000)*0.3
+MAX(0,MIN(1750000,GrossSales)-150000)*0.05

 

[Peo Sjoblom]

Another way courtesy of John McGimpsey

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

where A1 holds the 665,000.00

 

[Ron de Bruin]

Hi Peo

http://www.mcgimpsey.com/excel/taxvariablerate.html

John have a page about this