if command

[lynettecheong]

Totally clueless. Please help me to come up with a simple command to calculate commission payable based on the below condition

if net gain is
$8000-$11,999, payable at 4%
$12,000 - $20,999, payable at 7%
$21,000 - $29,999, payable at 10%
>= $30,000, one off payment at $3,000

i tried to modify some examples, but excel kept returning error

first attempt:
=IF(AND((N8)<=8000,(N8)<11999,4%,IF(AND((N8)>12000,(N8)<=20999),7%,IF(AND((N8)>21000,(N8)<=29999),10%,3000)))

2nd attempt:
=if(n8<7999,n8*0.04,if(n8<21000,n8*0.07),if(n8<30000,n8*0.10),if(n8>29000,3000))

I've spent the whole morning and afternoon on it and my boss is getting impatient! please help!!! thank you

[Dav]

Perhaps

Please Login or Register  to view this content.

Regards

Dav

[Cheeky Charlie]

=IF(N8>=30000,3000,N8*HLOOKUP(N8,{0,8000,12000,21000;0,0.04,0.07,0.1},2))

I assumed there's no commission for values less than 8000.

HTH

[lynettecheong]

Awww... many thanks with the fast response.
My boss is happy now