# Excel - IF Function Problem - Expert Solution

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

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.