Formula to divide out limited resources (marbles) to a group that wants more than available

[superskid]

[FONT=&quot]I need a formula in column D that will give everyone their marble allocation based on how many they want, and their score.[/FONT]
[FONT=&quot]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[/FONT]
[FONT=&quot]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[/FONT]
[FONT=&quot]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[/FONT]
[FONT=&quot]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.[/FONT]
[FONT=&quot]Screenshot:[/FONT]
[FONT=&quot]

[/FONT]

 

[Eric W]

This looks to be too complicated to do with formulas. Probably why you haven't gotten any responses so far. Even with VBA it's tricky. However, I think I can picture how to do it. I'm going to be away for a few days. If you haven't gotten an answer when I get back, I'll take a shot at a VBA macro for you.

 

[superskid]

This looks to be too complicated to do with formulas. Probably why you haven't gotten any responses so far. Even with VBA it's tricky. However, I think I can picture how to do it. I'm going to be away for a few days. If you haven't gotten an answer when I get back, I'll take a shot at a VBA macro for you.

Greatly appreciated, I have plugged away at this for weeks and can't quite get it.

 

[shg]

Cross-posted at https://www.mrexcel.com/forum/excel...up-wants-more-than-available.html#post5005274

 

[Eric W]

Turns out that you can do this with a formula:

	A	B	C	D	E	F	G	H
1	Name	Marbles Wanted	Score	Marbles Assigned			Variables
2	Billy	50	100	20	x		Marbles Available	100
3	Marry	50	85	12.91695			Max Marbles per person	20
4	Douglas	65	86	16.98959			Min Marbles per person	0.1
5	Mark	20	90	20	x		Top X get Max	3
6	Ben	3	50	0.455892			Marble difference per rank
7	Tom	20	25	1.519641
8	Wayne	40	110	20	x
9	Vicky	30	86	7.841349
10	Ales	7	13	0.276575
11
12	Totals	285		100

<tbody>
</tbody>

Sheet5​

Array Formulas Cell Formula D2 {=IF(C2>=LARGE($C$2:$C$10,$H$5),MIN(B2,$H$3),MIN($H$3,B2*C2/MAX($C$2:$C$10)*($H$2-SUM(IF($C$2:$C$10>=LARGE($C$2:$C$10,$H$5),IF($B$2:$B$10>$H$3,$H$3,$B$2:$B$10))))/SUM(IF($C$2:$C$10< LARGE($C$2:$C$10,$H$5),$B$2:$B$10*$C$2:$C$10/MAX($C$2:$C$10)))))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

This finds the top X rows by score, then assigns them the max amount. If there is a tie for 3rd place for example, both of them will get the max. Anyone else on the list will get marbles assigned proportionately based on their score, and on how many marbles are actually available.

I don't actually use the minimum marbles per person value, since the way this calculates, no one should get 0, unless their score is 0. But it wouldn't be hard to add to the formula, depending on what other tweaks are needed. I could also add ROUND to the formula to make it just 1 decimal.

Also, there is the possibility of some marbles not being assigned. Change the H5 value to 2 for example. This happens because at least 1 person not in the top X hit the max marbles per person condition. If you want to try to assign all the excess marbles to the other people, it would probably take some kind of iterative process. That's definitely a VBA thing, maybe Solver. And I don't know what the marbles represent, or how people decide on how many to request, but it would be easy for someone to get the max marbles just by requesting a large number (and it would throw off the calculations for everyone else).

I created the score ratio based on the person's score divided by the maximum score in column C, or 110. If the maximum score is 100, and that's extra credit or something, and you want the ratios based on 100, replace the MAX($C$2:$C$10) in the formula (2 places) with 100.


In any case, take a look and see if it works for you.