I have a commission base of £40000 to £80000 I have tried to put a statement
together but end up with negative or positive valves if go above or below
range
eg
"=IF($I$16=80000,(40000*$K$16),IF(I16<80000,( I16-40000*K16),IF(I16<40000,(0))))
using excel 2003 SP3

Bit of a guess as you didn't explain the rules

=IF($I$16=80000,40000*$K$16,IF(I16<80000,($I$16-40000)*K16,IF($I$16<40000,0)))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"floreman" wrote in message
...
I have a commission base of £40000 to £80000 I have tried to put a
statement
together but end up with negative or positive valves if go above or below
range
eg
"=IF($I$16=80000,(40000*$K$16),IF(I16<80000,( I16-40000*K16),IF(I16<40000,(0))))
using excel 2003 SP3

Hi

=IF(I16<40000,0,IF(I16<80000,(I16-40000*$K$16),40000*K16))

Regards,
Per

"floreman" skrev i meddelelsen
...
I have a commission base of £40000 to £80000 I have tried to put a
statement
together but end up with negative or positive valves if go above or below
range
eg
"=IF($I$16=80000,(40000*$K$16),IF(I16<80000,( I16-40000*K16),IF(I16<40000,(0))))
using excel 2003 SP3

HI Bob

This came back with a negative when I put in £100000 revenue figure i.

What I am trying to do is that people earn commission on
£0-£40000,£40000-£80000 and above £80000 @ 30%,35% & 40% so my problem is
with the middle formula getting a multiple if statement to look at what can
be earned between 40000 to 80000 eg 10000 comms @ 30%
50000 comms 40000@ 30% 10000@35% an so on I hope this explains better.

Regards

Mike

"Bob Phillips" wrote:

Bit of a guess as you didn't explain the rules

=IF($I$16=80000,40000*$K$16,IF(I16<80000,($I$16-40000)*K16,IF($I$16<40000,0)))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"floreman" wrote in message
...
I have a commission base of £40000 to £80000 I have tried to put a
statement
together but end up with negative or positive valves if go above or below
range
eg
"=IF($I$16=80000,(40000*$K$16),IF(I16<80000,( I16-40000*K16),IF(I16<40000,(0))))
using excel 2003 SP3

HI
What I am trying to do is that people earn commission on
£0-£40000,£40000-£80000 and above £80000 @ 30%,35% & 40% so my problem is
with the middle formula getting a multiple if statement to look at what can
be earned between 40000 to 80000 eg 10000 comms @ 30%
50000 comms 40000@ 30% 10000@35% an so on I hope this explains better.

Regards

Mike


"Per Jessen" wrote:

Hi

=IF(I16<40000,0,IF(I16<80000,(I16-40000*$K$16),40000*K16))

Regards,
Per

"floreman" skrev i meddelelsen
...
I have a commission base of £40000 to £80000 I have tried to put a
statement
together but end up with negative or positive valves if go above or below
range
eg
"=IF($I$16=80000,(40000*$K$16),IF(I16<80000,( I16-40000*K16),IF(I16<40000,(0))))
using excel 2003 SP3

Hi
This formula should do what you need. I have 30%, 35% and 40% in A1:A3, but
that can be changed as needed. The formula should be entered as one line.

=IF(I16<=40000,I16*$A$1,IF(I16<=80000,40000*$A$1+( (I16-40000)*$A$2),40000*$A$1+40000*$A$2+((I16-80000)*$A$3)))

Best regards,
Per

"floreman" skrev i meddelelsen
...
HI
What I am trying to do is that people earn commission on
£0-£40000,£40000-£80000 and above £80000 @ 30%,35% & 40% so my problem is
with the middle formula getting a multiple if statement to look at what
can
be earned between 40000 to 80000 eg 10000 comms @ 30%
50000 comms 40000@ 30% 10000@35% an so on I hope this explains better.

Regards

Mike


"Per Jessen" wrote:

Hi

=IF(I16<40000,0,IF(I16<80000,(I16-40000*$K$16),40000*K16))

Regards,
Per

"floreman" skrev i meddelelsen
...
I have a commission base of £40000 to £80000 I have tried to put a
statement
together but end up with negative or positive valves if go above or
below
range
eg
"=IF($I$16=80000,(40000*$K$16),IF(I16<80000,( I16-40000*K16),IF(I16<40000,(0))))
using excel 2003 SP3