Commission calculation (VBA or excel formula) for 3 levels

[simonpoh]

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;

Date Join	ID	Name	Amount $	Introducer ID	Name
01-10-12	A0001	Jack	1,000	-	-
03-10-12	A0002	John	1,000	A0001	Jack
03-10-12	A0003	Alex	1,000	A0001	Jack
04-10-12	A0004	Murphy	1,000	A0002	John
04-10-12	A0005	Douglas	1,000	A0002	John
05-10-12	A0006	Vincent	1,000	A0004	Murphy
05-10-12	A0007	Raphael	1,000	A0004	Murphy
06-10-12	A0008	Kate	1,000	A0006	Vincent
07-10-12	A0009	Cindy	1,000	A0008	Kate

<tbody>
</tbody>

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.

 

[Andrew Poulsom]

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.

 

[shg]

Nicely done, Andrew.

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

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

 

[Andrew Poulsom]

Nicely done, Andrew.

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

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

I prefer your solution because it's more transparent and scalable.

 

[simonpoh]

Thank you all for your kind help. =) Really appreciate it!

 

[simonpoh]

Nicely done, Andrew.

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

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

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.

	A	B	C	D
1	ID	A0001
2	Name	Jack
3
4	Level	ID	Name	Amount
5	1	A0002	John	100.00
6	1	A0003	Alex	100.00
7	2	A0004	Murphy	50.00
8	2	A0005	Douglas	50.00
9	3	A0006	Vincent	20.00
10	3	A0007	Raphael	20.00

<tbody>
</tbody>

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.