Leveled Profit Sharing Formula

[jvandal009]

I want to create a formula that gives me a point for anyone I recruit that does a deal. One full point. Then, to anyone they recruit, they get a point for every deal, and I get a half point. So on and so on. So all I have to do is put in how many deals at the end of the year and the point are added based on the levels above someone you are. The person who does the deal doesn't get any points, only their "family" above them.

Example: Adam recruits Bob. Bob recruits Charles. Charles recruits Dan.

Dan closes 1 deal. So Charles gets 1 point. Bob gets 1/2 a point, and Adam gets a 1/3 point.

Charles does 3 deals so Bob gets 3 points, and Adam gets 3- (1/2) points. Or 1.5 points.
Bob does 2 deals, so Adam is the only one who gets 2 points for Bob's deal.

I want it to be able to be very large and be able to have unlimited depth.

Thansk.

 

[StephenCrump]

Welcome to MrExcel!

This would probably more easily be done using VBA, if that's that an option?

Excel formulae won't accommodate an indeterminate no of levels. Here's one way you might approach it using helper columns. I have colour coded some cells to show how the points flow up through the pyramid.

E3: {=SUM(($A$3:$A$11=$J3:$L3)*($C$3:$C$11=$C3+1)*$B$3:$B$11)} array-entered, copy down

F3: {=SUM(($A$3:$A$11=$J3:$L3)*($C$3:$C$11=$C3+1)*OFFSET($D$3:$D$11,,E$2))*E$2/F$2} copy down and across

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K
1	Name	No of deals	Level	Total	Points	Points	Points	Points		Recruits
2					1	2	3	4
3	Adam	1	1	15.75	2.00	3.50	6.00	4.25		Bob
4	Bob	2	2	21.67	7.00	9.00	5.67	0.00		Charles	Chad
5	Charles	3	3	5.00	5.00	0.00	0.00	0.00		Dan
6	Chad	4	3	21.50	13.00	8.50	0.00	0.00		Diane	Dave
7	Dan	5	4	0.00	0.00	0.00	0.00	0.00
8	Diane	6	4	17.00	17.00	0.00	0.00	0.00		Ed	Evelyn
9	Dave	7	4	0.00	0.00	0.00	0.00	0.00
10	Ed	8	5	0.00	0.00	0.00	0.00	0.00
11	Evelyn	9	5	0.00	0.00	0.00	0.00	0.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​