I need a formula in column D that will give everyone their marble allocation based on how many they want, and their score.
Column A: Users Name Column B: How many marbles each person wants in a perfect scenario Column C: Their score or ranking within the group (higher score gets more) Column D: Here is where I want to input the formula, drag down and get the number
Variable: Marbles available: This is how many I can hand out, so total of column D needs to equal this Max Marbles per person: The most that any 1 person can be assigned Min Marbles per person: The amount of marbles the last person would get Top X get Max: If 3 is typed in here then the top 3 scores would get 20 then it would drop from there Marble difference per person: This is a formula that would say how much less then the person above you, you get after the top 3. So maybe each ranking after the top 3 the marbles assigned drops by 0.6 in order to end at the minimum number
Notes: If someone's assigned marbles is more then they want it needs to default them to them to their wanted amount and divide the rest up between everyone else not in the top 3
If people have the same score their allocation should be the same. It would only be different if one of the people want less then what their allocation would be.
Screenshot:
Z1w7jGX.png
Bookmarks