Sale commissions calculations needed

[Jacky]

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

 

[mr_ben]

Here you go....


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

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



HT

 

[Jacky]

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

 

[mr_ben]

Thanks Mr Ben,

However this calculates the 5% on the whole of the value if over th
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


Here you go....

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


(using the same assumptions

 

[Sandy Mann]

=IF(E11<D11*60%,E11*0%,IF(AND(E11>60.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%+((E11>D11)*(E11-D11)*2%))

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[mr_ben]

=IF(E11<D11*60%,E11*0%,IF(AND(E11>D11*60.1%,E11<D11*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 jus
calculates at a different rate

 

[Sandy Mann]

=IF(E11<D11*60%,E11*0%,IF(AND(E11>D11*60.1%,E11<D11*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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[mr_ben]

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 whol
lot better, I just got caught up with the AND and OR functions that
took my eye off the main question

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

 

[Jacky]

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.

 

[Sandy Mann]

(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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[daddylonglegs]

Perhaps

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