How would you form an equation to split a sum into 10 tiers, where each tier is multiplied by the first?

Question

I am trying to figure out a complex (for me) equation for a group payout.
The sum is changing every week and the amount of people is changing, but there is a tiered payout system where each tier up is multiplied by the first tier.

So the total payout is 578,355,415
If there were 33 members and we paid 32 of them a T1 payment they would get 13,770,367 and one T10 would get 137,703,671.

I am just unsure on how to set up an equation for this in excel if the number of members or payout changes.

Answer 1

32*13770367 / 578355415 = 76.19% for Tier zer0

 

137703671/578355415 = 23.81% for Tier 1

 

So you place the amount in the jackpot in a cell, say A5

 

Since the Tier 0 people get 76.19%, your formula is =0.7619*$a$5 in another cell, say B5

THen you do =B5/A6 where A6 contains the number of people in Tier 1.

 

You do similar calculations for tier 1.