Commision Function

[David]

Hello

I like to setup a sales commision function
i.e
commision between $0 to $5000 is 3%, $5001 to $10000 is 2%, $10001 to $20000 is 1%, above $20001 is 0.5

total sales is $8000 then the commision is $5000*3% + $3000*2% = $21
total sales is $21000 then the commision is $5000*3% + $5000*2% + $10000*1% + $1000*0.5% = $35

Thank

 

[Frank Kabel]

Hi David
try
=MIN(A1,5000)*0.03+MAX(MIN(A1,10000)-5000,0)*0.02+MAX(MIN(A1,20000)-100
00,0)*0.01+MAX(A1-20000,0)*0.005
where A1 stores your sales amount

Frank

 

[Chris Leonard]

Nice one ...

Hi David
try
=MIN(A1,5000)*0.03+MAX(MIN(A1,10000)-5000,0)*0.02+MAX(MIN(A1,20000)-100
00,0)*0.01+MAX(A1-20000,0)*0.005
where A1 stores your sales amount

Frank

 

[David]

Perfec

Thanks

 

[Peo Sjoblom]

Somewhat simpler taken from J.E. McGimpsey

=SUMPRODUCT(--(A1>{0;5000;10000;20000}),(A1-{0;5000;10000;20000}),{0.03;-0.0
1;-0.01;-0.005})

where A1 holds your commission

--

Regards,

Peo Sjoblom

Hello,

I like to setup a sales commision function.
i.e.
commision between $0 to $5000 is 3%, $5001 to $10000 is 2%, $10001 to

$20000 is 1%, above $20001 is 0.5%

 

[Frank Kabel]

Somewhat simpler taken from J.E. McGimpsey

=SUMPRODUCT(--(A1>{0;5000;10000;20000}),(A1-{0;5000;10000;20000}),{0.03
;-0.0
1;-0.01;-0.005})

Hi Peo
nice - though simpler?

Frank