Commission calculation (VBA or excel formula) for 3 levels

S

simonpoh

New Member
Joined
Nov 17, 2011
Messages
5
Hi all,

I need help to calculate commission based on 3 levels. I have a list of database with the date they join, their identification No. (ID), name, amount, the ID and person who introduce them.

Example;

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date Join[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Amount $[/TD]
[TD]Introducer ID[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]01-10-12[/TD]
[TD]A0001[/TD]
[TD]Jack[/TD]
[TD]1,000[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]03-10-12[/TD]
[TD]A0002[/TD]
[TD]John[/TD]
[TD]1,000[/TD]
[TD]A0001[/TD]
[TD]Jack[/TD]
[/TR]
[TR]
[TD]03-10-12[/TD]
[TD]A0003[/TD]
[TD]Alex[/TD]
[TD]1,000[/TD]
[TD]A0001[/TD]
[TD]Jack[/TD]
[/TR]
[TR]
[TD]04-10-12[/TD]
[TD]A0004[/TD]
[TD]Murphy[/TD]
[TD]1,000[/TD]
[TD]A0002[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]04-10-12[/TD]
[TD]A0005[/TD]
[TD]Douglas[/TD]
[TD]1,000[/TD]
[TD]A0002[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]05-10-12[/TD]
[TD]A0006[/TD]
[TD]Vincent[/TD]
[TD]1,000[/TD]
[TD]A0004[/TD]
[TD]Murphy[/TD]
[/TR]
[TR]
[TD]05-10-12[/TD]
[TD]A0007[/TD]
[TD]Raphael[/TD]
[TD]1,000[/TD]
[TD]A0004[/TD]
[TD]Murphy[/TD]
[/TR]
[TR]
[TD]06-10-12[/TD]
[TD]A0008[/TD]
[TD]Kate[/TD]
[TD]1,000[/TD]
[TD]A0006[/TD]
[TD]Vincent[/TD]
[/TR]
[TR]
[TD]07-10-12[/TD]
[TD]A0009[/TD]
[TD]Cindy[/TD]
[TD]1,000[/TD]
[TD]A0008[/TD]
[TD]Kate[/TD]
[/TR]
</tbody>[/TABLE]

Based on the above data, Jack (A0001) recruit John (A0002) and Alex (A0003). Jack suppose to receive a commission of $ 100 for each person he recruited (2 x $ 100 = $ 200). This is level 1.

John (A0002) then recruit Murphy (A0004) and Douglas (A0005). Jack (A0001) will receive $ 50 for each person John (A0002) recruited. (2 x $ 50 = $ 100). This is level 2 (aka overriding).

Murphy (A0004) then recruit Vincent (A0006) and Raphael (A0007). Jack (A0001) will receive $ 20 for each person Murphy (A0004) recruited. (2 x $ 20 = $ 40). This is level 3 (aka overriding).

How do I calculate the above by using VBA or excel formulae? Any help would be highly appreciated.

Thank you in advance.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Formulas:

G2 =IF(ISNA(MATCH(INDEX(F$2:F$10,MATCH(F2,C$2:C$10,FALSE)),C$2:C$10,FALSE)),"",INDEX(F$2:F$10,MATCH(INDEX(F$2:F$10,MATCH(F2,C$2:C$10,FALSE)),C$2:C$10,FALSE)))
H2 =SUM(COUNTIF(F$2:F$10,C2)*100,SUMPRODUCT(--(F$2:F$10=C2),COUNTIF(F$2:F$10,C$2:C$10))*50,COUNTIF(G$2:G$10,C2)*20)

copied down.
 
Upvote 0
Nicely done, Andrew.

Mine is somewhat similar. I deleted col F, and then:


Code: 
       ----A----- --B-- ---C--- ---D---- --E-- --F-- --G-- --H--
   1      Join     ID    Name   Amount $  100   50    20   Total
   2   01/10/2012 A0001 Jack       1,000 -                   340
   3   03/10/2012 A0002 John       1,000 A0001 -             320
   4   03/10/2012 A0003 Alex       1,000 A0001 -               0
   5   04/10/2012 A0004 Murphy     1,000 A0002 A0001 -       270
   6   04/10/2012 A0005 Douglas    1,000 A0002 A0001 -         0
   7   05/10/2012 A0006 Vincent    1,000 A0004 A0002 A0001   150
   8   05/10/2012 A0007 Raphael    1,000 A0004 A0002 A0001     0
   9   06/10/2012 A0008 Kate       1,000 A0006 A0004 A0002   100
  10   07/10/2012 A0009 Cindy      1,000 A0008 A0006 A0004     0


E1:G1 are inputs (the tier values)

In F3 and copied across and down to G10: =IFERROR(INDEX($E$2:$E2, MATCH(E3, $B$2:$B2, 0)), "")

In H2 and copied down: =SUMPRODUCT($E$1:$G$1 * ($E$2:$G$10=B2))
 
Upvote 0
shg said:
Nicely done, Andrew.

Mine is somewhat similar. I deleted col F, and then:


Code: 
       ----A----- --B-- ---C--- ---D---- --E-- --F-- --G-- --H--
   1      Join     ID    Name   Amount $  100   50    20   Total
   2   01/10/2012 A0001 Jack       1,000 -                   340
   3   03/10/2012 A0002 John       1,000 A0001 -             320
   4   03/10/2012 A0003 Alex       1,000 A0001 -               0
   5   04/10/2012 A0004 Murphy     1,000 A0002 A0001 -       270
   6   04/10/2012 A0005 Douglas    1,000 A0002 A0001 -         0
   7   05/10/2012 A0006 Vincent    1,000 A0004 A0002 A0001   150
   8   05/10/2012 A0007 Raphael    1,000 A0004 A0002 A0001     0
   9   06/10/2012 A0008 Kate       1,000 A0006 A0004 A0002   100
  10   07/10/2012 A0009 Cindy      1,000 A0008 A0006 A0004     0


E1:G1 are inputs (the tier values)

In F3 and copied across and down to G10: =IFERROR(INDEX($E$2:$E2, MATCH(E3, $B$2:$B2, 0)), "")

In H2 and copied down: =SUMPRODUCT($E$1:$G$1 * ($E$2:$G$10=B2))
Click to expand...

I prefer your solution because it's more transparent and scalable.
 
Upvote 0
shg said:
Nicely done, Andrew.

Mine is somewhat similar. I deleted col F, and then:


Code: 
       ----A----- --B-- ---C--- ---D---- --E-- --F-- --G-- --H--
   1      Join     ID    Name   Amount $  100   50    20   Total
   2   01/10/2012 A0001 Jack       1,000 -                   340
   3   03/10/2012 A0002 John       1,000 A0001 -             320
   4   03/10/2012 A0003 Alex       1,000 A0001 -               0
   5   04/10/2012 A0004 Murphy     1,000 A0002 A0001 -       270
   6   04/10/2012 A0005 Douglas    1,000 A0002 A0001 -         0
   7   05/10/2012 A0006 Vincent    1,000 A0004 A0002 A0001   150
   8   05/10/2012 A0007 Raphael    1,000 A0004 A0002 A0001     0
   9   06/10/2012 A0008 Kate       1,000 A0006 A0004 A0002   100
  10   07/10/2012 A0009 Cindy      1,000 A0008 A0006 A0004     0


E1:G1 are inputs (the tier values)

In F3 and copied across and down to G10: =IFERROR(INDEX($E$2:$E2, MATCH(E3, $B$2:$B2, 0)), "")

In H2 and copied down: =SUMPRODUCT($E$1:$G$1 * ($E$2:$G$10=B2))
Click to expand...

Hi,

I've used the method above but I have another problem. What if I need to track who are the people under A0001 Jack?
I need to list it in another spreadsheet. It will be like a statement to Jack.

For eg.

[TABLE="width: 50"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID[/TD]
[TD]A0001[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Name[/TD]
[TD]Jack[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Level[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: center"]1[/TD]
[TD]A0002[/TD]
[TD]John[/TD]
[TD="align: right"]100.00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: center"]1[/TD]
[TD]A0003[/TD]
[TD]Alex[/TD]
[TD="align: right"]100.00[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: center"]2[/TD]
[TD]A0004[/TD]
[TD]Murphy[/TD]
[TD="align: right"]50.00[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: center"]2[/TD]
[TD]A0005[/TD]
[TD]Douglas[/TD]
[TD="align: right"]50.00[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: center"]3[/TD]
[TD]A0006[/TD]
[TD]Vincent[/TD]
[TD="align: right"]20.00[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: center"]3[/TD]
[TD]A0007[/TD]
[TD]Raphael[/TD]
[TD="align: right"]20.00[/TD]
[/TR]
</tbody>[/TABLE]

I would really appreciate it if you guys can help me out or with any other alternative rather than the above I've just posted.

Thank in advance. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

airforceone
  • Solved
Append Value from 3 worksheet to the Right of a New Worksheet
2
Replies
17
Views
686
airforceone
airforceone
E
  • Question Question
Calculating Tiered Commission Structure based on Rolling Annual Contribution
Replies
1
Views
1K
Dave Patton
D
B
  • Question Question
Using Find with 2D Arry
2 3
Replies
26
Views
2K
Akuini
Akuini
C
  • Solved
VBA to merge some columns from 2 excel sheets into 1 master sheet
Replies
5
Views
1K
tonyyy
tonyyy
W
  • Question Question
Create a VBA or formula that can summarise all the info from different sheets that does not have the same description cells
Replies
0
Views
472
Waffles255
W

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top