Question description:
This user has given permission to use the problem statement for this
blog.
Trying to work out a formula to calc commission.
If 100% of current month achieved com's =3%
if Subsequent months are at 80 and 60% additional 1% of current month achieved.
example if targets are for 3 months are £100 each and you achieve £100 £80 £60 commission would be calculated at 5%
in addition for every 10% over the current months target an additional 1% achieved capped at 150%
so if you achieve 150% of current month and in addition achieve 80% and 60% respectively a total of 10% of current month is paid is there a best formula to achieve these % figures
Solved by E. J. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
07/08/2018 - 05:54
Hi there
User
07/08/2018 - 05:54
hi
Excelchat Expert
07/08/2018 - 05:54
How are you and how may i assist you with today?
User
07/08/2018 - 05:55
trying to work a commission calculator
Excelchat Expert
07/08/2018 - 05:55
Okay, i will try my best to help you out with this
User
07/08/2018 - 05:55
works in three month cycles
Excelchat Expert
07/08/2018 - 05:55
Can you share me a sample worksheet
Excelchat Expert
07/08/2018 - 05:55
So that we both can have a better evaluation to the issue
User
07/08/2018 - 05:56
can you see the shared sheet
Excelchat Expert
07/08/2018 - 05:56
Yeah, please go ahead :)
User
07/08/2018 - 05:58
does that make sense ?
Excelchat Expert
07/08/2018 - 05:59
okay, so overall we can say for three months out of 300 targets #60 were not fullfilled, right?
User
07/08/2018 - 06:00
so you get the additional 1%commission only if you are on 80% next month and 60% following month
User
07/08/2018 - 06:00
if 80 and 60 %'s not achied then just 3% for achieving current motnh ?
User
07/08/2018 - 06:01
forward bookings
Excelchat Expert
07/08/2018 - 06:01
Okay, we just play around the %age right?
User
07/08/2018 - 06:01
yes
User
07/08/2018 - 06:01
then to complicate further
User
07/08/2018 - 06:02
every 10% over current month earns additional 1%
Excelchat Expert
07/08/2018 - 06:03
so, now for sept we can see there is a decrease of 20%, right?
User
07/08/2018 - 06:04
no sorry
User
07/08/2018 - 06:04
if in Aug im on 100%
User
07/08/2018 - 06:04
and in sept im on 80% and oct 60%
User
07/08/2018 - 06:04
commision = £3% for 100% aug
User
07/08/2018 - 06:05
plus 1% for being on 80% Sept
User
07/08/2018 - 06:05
plus 1 % for achieving 60% oct
Excelchat Expert
07/08/2018 - 06:07
Is there any base amount? like to add 3% on that or something like that around?
User
07/08/2018 - 06:08
only get 3% if 100%of current month achieved !
Excelchat Expert
07/08/2018 - 06:08
And what if Aug is not 100% achieved?
User
07/08/2018 - 06:08
nothing
User
07/08/2018 - 06:08
have to be 100% of current moth to achieve anything
Excelchat Expert
07/08/2018 - 06:09
Can you see the formula in column B?
User
07/08/2018 - 06:09
commission would by £3
Excelchat Expert
07/08/2018 - 06:10
Okay, just put you condition replacing the 3% in the formula
User
07/08/2018 - 06:11
so B6 would be 3% if B5 = 100 however if D5 = 80% B6 would be 4%
User
07/08/2018 - 06:12
% of B£
User
07/08/2018 - 06:12
B3
Excelchat Expert
07/08/2018 - 06:14
so its like if target is 100% the commission would be 3% and if D6= 80% then commission would be +1% that is 4 % right?
User
07/08/2018 - 06:14
correct and if f3 was 60% would be 5%
User
07/08/2018 - 06:16
so commission earned if 100 / 80 / 60 achieved would be 5% of B3 = £5
User
07/08/2018 - 06:20
however if only 50% of October achieved commission in August would only be 4%
User
07/08/2018 - 06:20
if that makes sense
Excelchat Expert
07/08/2018 - 06:21
Is the value correct in B7?
User
07/08/2018 - 06:21
yes
Excelchat Expert
07/08/2018 - 06:22
Cool
User
07/08/2018 - 06:22
if actual in octobe changes to 50
Excelchat Expert
07/08/2018 - 06:22
okay
User
07/08/2018 - 06:22
would it change to 4%?
User
07/08/2018 - 06:22
or 4
Excelchat Expert
07/08/2018 - 06:23
then commision would become 0 as per the formula now, but we can manupulate that to get 4% as per your condition
User
07/08/2018 - 06:23
yes please
Excelchat Expert
07/08/2018 - 06:24
Check now
User
07/08/2018 - 06:25
chged oct commission goes to £0
User
07/08/2018 - 06:25
should be 4% of Aug if possible ?
User
07/08/2018 - 06:26
likewise if sept less than 80
User
07/08/2018 - 06:26
august commission should on be 3%
Excelchat Expert
07/08/2018 - 06:27
okay, cool lets go step by step :) so till now all seems to be good and the comission becomes 5 if we have 100%, 80%, 60%
User
07/08/2018 - 06:27
yes
User
07/08/2018 - 06:28
so if not 60% then commission =4
User
07/08/2018 - 06:28
and if not 80% commision should = 3
User
07/08/2018 - 06:28
if below not if not!
Excelchat Expert
07/08/2018 - 06:29
okay
Excelchat Expert
07/08/2018 - 06:31
Cool i guess i did that
Excelchat Expert
07/08/2018 - 06:31
Please have a check :)
User
07/08/2018 - 06:31
october works
User
07/08/2018 - 06:31
thank you
User
07/08/2018 - 06:31
however changes sept to 70 not 80
User
07/08/2018 - 06:31
and no change should reduce to 3
Excelchat Expert
07/08/2018 - 06:32
Yep understood, let me see that
Excelchat Expert
07/08/2018 - 06:33
D6 would be 3 % right?
User
07/08/2018 - 06:34
no - sorry if d3 is less than 80 b7 should be 3 or 3%
User
07/08/2018 - 06:35
this is all to work commission for current month
User
07/08/2018 - 06:35
just for august ?
Excelchat Expert
07/08/2018 - 06:36
If D5 < than 80 then 3 right?
User
07/08/2018 - 06:37
right
User
07/08/2018 - 06:37
as long as aug = 100%
Excelchat Expert
07/08/2018 - 06:38
Cool; check it now please
User
07/08/2018 - 06:38
all works great however....
User
07/08/2018 - 06:39
changed august to less than 100 and still shows 3
Excelchat Expert
07/08/2018 - 06:39
Okay what should be the condition for this?
User
07/08/2018 - 06:39
if aug less than 100 commission = 0
User
07/08/2018 - 06:39
no other rules apply
Excelchat Expert
07/08/2018 - 06:40
Cool, done that too :)
User
07/08/2018 - 06:40
you are a genius
User
07/08/2018 - 06:41
there is one more rule...... no idea if possible
Excelchat Expert
07/08/2018 - 06:41
Please do a valuable rating for me
Excelchat Expert
07/08/2018 - 06:41
that will help me a lot
Excelchat Expert
07/08/2018 - 06:41
Yep, please go ahead lets see that too
Excelchat Expert
07/08/2018 - 06:42
And Thanks a lot for the appreciation :)
User
07/08/2018 - 06:42
every 10% over current month only (aug) ANOTHER 1% to a total of 150% so total comms would be 10%
User
07/08/2018 - 06:42
make sense ?
User
07/08/2018 - 06:42
possible ?
Excelchat Expert
07/08/2018 - 06:43
Okay, can you be more specific for this like an example would be good
User
07/08/2018 - 06:43
actually this rule comes first
User
07/08/2018 - 06:43
so sorry
User
07/08/2018 - 06:43
if aug achieved = 100 then 3%
User
07/08/2018 - 06:44
if aug = 110% plus 1%
User
07/08/2018 - 06:44
120 =2%
Excelchat Expert
07/08/2018 - 06:44
Well it can be manipulated though so no worries :)
User
07/08/2018 - 06:44
as well as what you have already done
User
07/08/2018 - 06:44
for example i hit 150% of Aug target
User
07/08/2018 - 06:44
and 80 / 60 of following month
User
07/08/2018 - 06:45
max commission would be 10%
Excelchat Expert
07/08/2018 - 06:46
So in brief it would be like setting a cap of 10% commision if we hit 150, right?
User
07/08/2018 - 06:46
yes
User
07/08/2018 - 06:47
and if i achieved say 120% of target and NOT the 80 /60 i would earn 5% 3 for 100 of current and 2 for additional 10%'s over
User
07/08/2018 - 06:47
can i send you a thank you direct you have been amazing
Excelchat Expert
07/08/2018 - 06:47
okay if 110 / 80/ 60 then commission would be 4 right?
User
07/08/2018 - 06:47
no
User
07/08/2018 - 06:48
if 110 then 1 for 100 1 for extra 10 1 for 80 and 1 for 60
User
07/08/2018 - 06:48
so you were right sorry
User
07/08/2018 - 06:48
actually no
Excelchat Expert
07/08/2018 - 06:48
Yep you can send me by clicking on others, while you do the rating for me and that would really help me a lot Sir/Maam :)
User
07/08/2018 - 06:49
if 110 you would get 3 for 100 -
User
07/08/2018 - 06:49
1 for 110
User
07/08/2018 - 06:49
and 1 for 80 and 1 for 60
User
07/08/2018 - 06:50
so total would be 5
User
07/08/2018 - 06:50
no 6 sorry
User
07/08/2018 - 06:51
i cant count !!!
Excelchat Expert
07/08/2018 - 06:51
Check the below formula for 110
Excelchat Expert
07/08/2018 - 06:52
It was really nice talking to you and assisting you as time is ticking out
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
