I'm looking to distribute product by rank (there will be no ties in the ranking) to 4 stores where I want to fill into as much of the need as possible in as many of the stores as possible.
I think I'm looking for a solution that is very similar to this one.
=IF(SUMPRODUCT(($B$2:$E$2<=B2)*$F$2:$I$2)<=$K$2,B2,MAX($K$2-SUMPRODUCT(($B$2:$E$2<=(B2-1))*$F$2:$I$2),0))
The problem with the above solution is that it allocates in a "winner take all" way. So if you have 3 units available to send it will tend to fill into the total demand of the highest ranking first and then distribute what's left over, filling into total demand of each subsequent store, until reaching zero available units.
I want a more even distribution of resources. For example, if I have three units available to send and I have nine stores with a demand of 18 units each, I'd want the top three stores to receive one unit each. Or, another way, if I have nine stores with a demand of two each (total 18 demand) and I have 17 units available, I'd want the top eight stores to receive 2 units and the bottom store to receive one unit.
Using the data as in Evil Blue Monkey's answer you could use the following formula in L2:
=IF(K2,SORTBY(REDUCE({0,0,0,0},SEQUENCE(K2),LAMBDA(x,y,x+DROP(REDUCE(0,(x<SORTBY(F2:I2,B2:E2))*(SUM(x)<K2),LAMBDA(a,b,HSTACK(a,(SUM(x,a,b)<=K2)*b))),,1))),XMATCH({1,2,3,4},B2:E2)),{0,0,0,0})
This spills the result for that row over 4 cells to the right.
It starts counting at 0 for each store and adds 1 per store each iteration, where works in order of ranking it checks if the total sum does not exceed the availability and the total sum per store doesn't exceed the demand.
