Please help Im confused with if, or's and and formula

if sales target is say 1000
if they dont reach 60% no commission payable
if the reach 60% and over to sales target they get 3%
if they get over target they get 5% of everything over target

Here you go....


=IF(E11<D11*60%,E11*0%,IF(AND(E1160.1%,E11<100%), E11*3%,E11*5%))

Whereby
E11 = the amount achieved
D11 = the Target
and the percentages are (obviously) what you want.



HTH


--
mr_ben
------------------------------------------------------------------------
mr_ben's Profile: http://www.excelforum.com/member.php...o&userid=37106
View this thread: http://www.excelforum.com/showthread...hreadid=568720

Thanks Mr Ben,

However this calculates the 5% on the whole of the value if over the target.
I only want the difference over the sales target to get 5%

1000 target

achieve 1010

so this formula calculates & states pay 5% on 1010= 50.40
It should be 3% on 1000 & only 5% on the 10 (1010achived -1000 target)
which equals 30.50

Hope you can help

Jacky Wrote:
Thanks Mr Ben,

However this calculates the 5% on the whole of the value if over the
target.
I only want the difference over the sales target to get 5%

1000 target

achieve 1010

so this formula calculates & states pay 5% on 1010= 50.40
It should be 3% on 1000 & only 5% on the 10 (1010achived -1000 target)
which equals 30.50

Hope you can help

ahhh gotcha trying to paythem lessI like it ;) :p


Here you go....

=IF(E11<D11*60%,E11*0%,IF(AND(E1160.1%,E11<100%), E11*3%,D11*3%+(E11-D11)*5%))


(using the same assumptions)


--
mr_ben
------------------------------------------------------------------------
mr_ben's Profile: http://www.excelforum.com/member.php...o&userid=37106
View this thread: http://www.excelforum.com/showthread...hreadid=568723

mr_ben" wrote in
message ...

=IF(E11<D11*60%,E11*0%,IF(AND(E1160.1%,E11<100%), E11*3%,D11*3%+(E11-D11)*5%))

This returns errors for me. For example with 1000 in D11 and 700 in E11 the
formula returns 15 whereas 3% of 700 is 21

Try:

=IF((E11/D11)<60%,0,E11*3%+((E11D11)*(E11-D11)*2%))

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"mr_ben" wrote in
message ...

Jacky Wrote:
Thanks Mr Ben,

However this calculates the 5% on the whole of the value if over the
target.
I only want the difference over the sales target to get 5%

1000 target

achieve 1010

so this formula calculates & states pay 5% on 1010= 50.40
It should be 3% on 1000 & only 5% on the 10 (1010achived -1000 target)
which equals 30.50

Hope you can help

ahhh gotcha trying to paythem lessI like it ;) :p


Here you go....

=IF(E11<D11*60%,E11*0%,IF(AND(E1160.1%,E11<100%), E11*3%,D11*3%+(E11-D11)*5%))


(using the same assumptions)


--
mr_ben
------------------------------------------------------------------------
mr_ben's Profile:
http://www.excelforum.com/member.php...o&userid=37106
View this thread: http://www.excelforum.com/showthread...hreadid=568723

=IF(E11<D11*60%,E11*0%,IF(AND(E11D11*60.1%,E11<D1 1*100%),
E11*3%,D11*3%+(E11-D11)*5%))


miss read the formula (oops again)

(this does work and the second formula is not really an error it just
calculates at a different rate)


--
mr_ben
------------------------------------------------------------------------
mr_ben's Profile: http://www.excelforum.com/member.php...o&userid=37106
View this thread: http://www.excelforum.com/showthread...hreadid=568723