|
Sale commissions calculations needed
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 |
Sale commissions calculations needed
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 |
Sale commissions calculations needed
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 |
Sale commissions calculations needed
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 |
Sale commissions calculations needed
=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 |
Sale commissions calculations needed
"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 |
Sale commissions calculations needed
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 |
Sale commissions calculations needed
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. |
Sale commissions calculations needed
"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 |
Sale commissions calculations needed
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 |
| All times are GMT +1. The time now is 03:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com