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