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

"mr_ben" wrote in
message ...

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

What happens if E11 is between 60% and 60.1% of D11?

--
HTH

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"mr_ben" wrote in
message ...

=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

you're buggered!! lol (and tell them to work harder)

(or you could just delete the .1 )

admittedly your formula is shorter and sweeter and does the job a whole
lot better, I just got caught up with the AND and OR functions that I
took my eye off the main question.


I did think about that but couldn't see a way round as said yours does
it with having to worry about that problem


--
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 & Sandy Mann

My many many thanks. I learnt all these types of things years ago..

but as they say if you dont use it ya loose it, & I certainly have.

this is exactly what I needed. Thanks for your help.

"mr_ben" wrote in
message ...

(or you could just delete the .1 )

That would just leave exactly 60%

One way would be to use greater than or equal to (=)

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

Incidentally E11*0% = 0% (or just 0) and D11 * 100% = D11 so you could just
as well use:

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


--
Regards,

Sandy
In Perth, the ancient capital of Scotland


with @tiscali.co.uk


"mr_ben" wrote in
message ...

you're buggered!! lol (and tell them to work harder)

(or you could just delete the .1 )

admittedly your formula is shorter and sweeter and does the job a whole
lot better, I just got caught up with the AND and OR functions that I
took my eye off the main question.


I did think about that but couldn't see a way round as said yours does
it with having to worry about that problem


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

Perhaps

=(E11=D11*0.6)*(E11*3%+MAX(0,E11-D11)*2%)


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