Excel Screenshot

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.

Excel Screenshot

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.

• excel
• excel-formula

3 Answers 3

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.

I don't know if there might be a more compact solution, but this one should work (paste it in a new sheet):

Here is a different approach where I've tried to share the units in accordance with demand, while giving some priority to the higher ranks.

Method - Starting with the highest ranked store, share the remaining units out in accordance with the demands of the remaining stores. If the result for the current store is not a whole number, round it up. Then re-evaluate the remaining units for the next store in the same way. The basic formula is

Remaining Units / Remaining demand * Current demand

=LET(ranks,B2:E2, demands,F2:I2, avail,K2, seq,SEQUENCE(1,4), INDEX(DROP(REDUCE(0,seq, LAMBDA(a,c,HSTACK(a,ROUNDUP((avail-SUM(a))/ SUMIFS(demands,ranks,">="&c)*XLOOKUP(c,ranks,demands),0)))),,1),ranks))

Where total amount available exceeds total demand e.g. for Socks#4, there should be a check that amount issued for each shop does not exceed demand:

=LET(ranks,B7:E7,demands,F7:I7,avail,K7,seq,SEQUENCE(1,4),INDEX(DROP(REDUCE(0,seq,LAMBDA(a,c,HSTACK(a,ROUNDUP(MIN(1,(avail-SUM(a))/SUMIFS(demands,ranks,">="&c))*XLOOKUP(c,ranks,demands),0)))),,1),ranks))

If you were to add a check for the availability being zero, the whole thing would look like this:

=LET(ranks,B2:E2, demands,F2:I2, avail,K2, seq,SEQUENCE(1,4), INDEX(DROP(REDUCE(0,seq,LAMBDA(a,c,HSTACK(a, IFERROR(ROUNDUP(MIN(1,(avail-SUM(a))/SUMIFS(demands,ranks,">="&c))*XLOOKUP(c,ranks,demands),0),0)))),,1),ranks))