# Leveled Profit Sharing Formula

#### jvandal009

##### New Member
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.

Last edited:

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### StephenCrump

##### MrExcel MVP
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
ABCDEFGHIJK
1NameNo of dealsLevelTotalPointsPointsPointsPointsRecruits
21234
5Charles335.005.000.000.000.00Dan
7Dan540.000.000.000.000.00
8Diane6417.0017.000.000.000.00EdEvelyn
9Dave740.000.000.000.000.00
10Ed850.000.000.000.000.00
11Evelyn950.000.000.000.000.00

</tbody>
Sheet1

Replies
2
Views
351
Replies
6
Views
472
Replies
5
Views
1K
Replies
0
Views
373
Replies
5
Views
136

1,195,582
Messages
6,010,581
Members
441,557
Latest member
Jbest23

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

### Which adblocker are you using?

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

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