Allocating Intergers based on Relative Percentage

[Jim Thomlinson]

Here is the Scenario. I have 15 different locations all of different relative
size. I will be shipping inventory to those locations based on their relative
size. The relative size is expressed as their % of the sum of all stores.
Here is what I need. I want a formula to allocate the units. The trick is
that units are integers and all units must be allocated. No more and no less.
Here is some sample data

6 Units
1 6.7%
2 6.2%
3 12.6%
4 5.2%
5 2.7%
6 7.9%
7 5.5%
8 8.8%
9 5.0%
10 7.7%
11 3.7%
12 8.1%
13 9.1%
14 7.3%
15 3.5%


Stores 1 through 15 and their relative size. I have 6 units. What formula
can I use to fairly allocate them as whole numbers. We do not need to worry
about ties in the % values as they are all unique.

 

[Luke M]

As your initial setup implies that some stores will not get any units, what
are the rules/constraints determining which stores (or how many) must receive
units?

In other words, why not give all the units to the highest ranked store?

 

[Bob Phillips]

Is this what you mean Jim?

Sort the percentages into a separate column

B1: =LARGE($B$1:$B$15,ROW(A1))

copy down

Assuming units is in G1

D1: =CEILING($G$1*C1,1)
D2: =MAX(CEILING(($G$1-SUM($D$11))*C2,1),0)

copy D2 down

You might want a lookup to get the location associated with the percentage

 

[Jim Thomlinson]

That is not that far off what I had. The issue with that is when I put in 30
units only 26 get allocated. Where do the other 4 go?
--
HTH...

Jim Thomlinson

Is this what you mean Jim?

Sort the percentages into a separate column

B1: =LARGE($B$1:$B$15,ROW(A1))

copy down

Assuming units is in G1

D1: =CEILING($G$1*C1,1)
D2: =MAX(CEILING(($G$1-SUM($D$11))*C2,1),0)

copy D2 down

You might want a lookup to get the location associated with the percentage

 

[Bob Phillips]

Logic error in formula from D2 down. Try

=MAX(MIN($G$1-SUM($D$11),CEILING($G$1*C2,1)),0)

--
__________________________________
HTH

Bob

That is not that far off what I had. The issue with that is when I put in
30
units only 26 get allocated. Where do the other 4 go?

 

[JoeU2004]

That is not that far off what I had. The issue with that is when I
put in 30 units only 26 get allocated. Where do the other 4 go?

That does not surprise me. Bob's math does make sense to me. Perhaps Bob
can explain it.

D2: =MAX(CEILING(($G$1-SUM($D$11))*C2,1),0)

That computes the percentage of total units (G1*C2) minus the cumulative sum
of already-allocated units times the percentage of total. It is the last
part that does not make sense to me mathematically: multiplying less than
total units by a percentage of the total.

I believe I could correct the mathematics.

But try the following instead, assuming the percentages are in B2:B16, total
units is in C1, and the allocated units are in C2:C16:

C2: =ROUND(B2*$C$1,0)
C3: =ROUND(SUM($B$2:B3)*$C$1,0) - SUM($C$2:C2)

Copy C3 down through C16.

You can choose to sort B2:B16 or not.

I don't know if this allocation will fit your definition of "fairness".
With a total of 26 units and B2:B16 is sorted in descending order, a
location that should receive 6.2% gets less (1) than a location that should
receive 5.5% (2).

But I suspect that is the nature of the beast, due to the quantization
requirement. Consider the case where all locations get about 6.7%
(100%/15). Some will have to get less than others. Similar, consider the
case where one location gets 75% and all other locations get about 1.8%
(25%/14). Some will have to get more than others.


----- original message -----

That is not that far off what I had. The issue with that is when I put in
30
units only 26 get allocated. Where do the other 4 go?

 

[Jim Thomlinson]

Thanks Bob... I swapped out the Ceiling to a Round otherwise the last stores
in the list got badly under-allocated. That meant that I had to plug the last
value...

D1: =ROUND($G$1*C1,0)
D2: =MAX(MIN($G$1-SUM($D$11),ROUND($G$1*C2,0)),0)
D:15: =G1-SUM(D114)

Additionally it does not require the list to be sorted which suits my
purpose...

 

[Jim Thomlinson]

I am wrong. I was retesting with larger numbers. When I went back to the
smaller numbers it gets messed up... Back to your formula and I will need to
tweak it for fairness so the last stores get a more equatable allocation.

For example with your formula and 100 items to allocate the last store get 0
and the one above only gets 4.

 

[JoeU2004]

I swapped out the Ceiling to a Round otherwise the last stores
in the list got badly under-allocated.

I understand your concern, especially with other distributions. But I don't
think Bob's formula works with ROUND, in general. I think you discovered
that, evidenced by the addition of your change in D15.

But with only 6 units, your kludge would allocate 3 to the lowest-percentage
location (2.7%), 1 to the 3 highest-percentage locations (8.8% to 12.6%),
and zero to the 11 in between (3.5% to 8.1%). Does that meet your sense of
"fair" allocation?


----- original message -----

 

[Jim Thomlinson]

Using your formulas with a sorted list the allocation is not correct. It
almost appears random (I know it is not but that is how it appears). I shall
continue to bang my head in disbelief that something that appears so simple
is getting the best of me.

 

[Jim Thomlinson]

Here is what I now. It seems to allocate a bit more equitably and works with
both large and small numbers... Percentages must be sorted...

My addition does this. If the store is getting 1 or more items then it
rounds, otherwise it uses the ceiling. Because I am rounding I need to plug
the last number...

D1: =MAX(MIN(CEILING($G$1*C2,1), IF($G$1*C2>1, ROUND($G$1*C2, 0), 1)),0)

D2: =MAX(MIN($G$1-SUM($D$11),CEILING($G$1*C2,1), IF($G$1*C2>1,
ROUND($G$1*C2, 0), 1)),0)

D15: =G1-SUM(D114)

I will do some more testing but this might be it....

 

[Jim Thomlinson]

Typo in D1...

D1: D1: =MAX(MIN(CEILING($G$1*C1,1), IF($G$1*C1>1, ROUND($G$1*C1, 0), 1)),0)

Still not perfect but darn close. Good to +/- 1 unit. It does lean towards
every location getting at least 1 unit before any store gets a second. I have
decided to call that a feature and not a mistake.

Bob and JoeU2004. Thanks for all of the input...

 

[Bernd P]

Hello Jim,

I suggest to apply the d'Hondt method:
http://sulprobil.com/html/d_hondt_method.html

Regards,
Bernd

 

[Jim Thomlinson]

Interesting but I have an issue with it in that it requires helper columns
and a significant number of them. It I have 15 locations and 100 items to
distribute I need 1500 rows of helper. On the sheet I am working on I have
120 stores and a few hundred products, each of which could have up to a few
hundred items to allocate. The formulas listed use Row() and as such are
voltile. If I implimented this it would dim the lights on the death star...

That being said I will keep this one in my back pocket for another day.

 

[JoeU2004]

I think the following has a similar distribution in many cases, but I think
it has a better distribution for some totals, for example 19, 20, 26 27, and
30.

For 27 in particular, the following avoids an anomaly of your formula,
namely that the 2.7% location receives 3 while the 3.5% and 3.7% locations
receive only 1.

The following assumes that locations are sorted in descending order of
percentage.

D1: =MAX(1, ROUND($G$1*C1,0))

D2: =MIN(MAX(1, ROUND(($G$1-SUM($D$11))*C2/SUM(C2:$C$15),0)),
$G$1-SUM($D$11))

Theory of operation: Generally, the allocation is determined based on the
percentage of the remaining units, rounded. MAX(1,...) adjusts the first
allocations less than 1. MIN(0,...) guards against MAX(1,...) adjustment
from exceeding the total units artificially.


----- original message -----

 

[Bob Phillips]

The original problem was that it was allocating the percentage on the
reduced number, not the original number.

 

[Bob Phillips]

I thought about round, and came up with

D1: =MAX(ROUND($G$1*C1,0),1)
D2: =MAX(MIN($G$1-SUM($E$1:E1),ROUND($G$1*C2,0)),0)

 

[Glenn]

Interesting but I have an issue with it in that it requires helper columns
and a significant number of them. It I have 15 locations and 100 items to
distribute I need 1500 rows of helper. On the sheet I am working on I have
120 stores and a few hundred products, each of which could have up to a few
hundred items to allocate. The formulas listed use Row() and as such are
voltile. If I implimented this it would dim the lights on the death star...

That being said I will keep this one in my back pocket for another day.

Well, it doesn't necessarily need helper columns, although there are some
limits. Assuming your percentages are in A1:A15 (and A16:A73 are empty), and
your number of units is in C1, put the following array formula (commit with
CTRL+SHIFT+ENTER) in B1 and copy down to B15:

=SUM(--(($A1/ROW(INDIRECT("$2:$74")))+
(ROW(INDIRECT("$2:$74"))/POWER(4,16))>=
LARGE(MMULT(($A$1:$A$74)+0,
TRANSPOSE(1/ROW(INDIRECT("$2:$74")))),C$1)))

Might not work for you due to the number of stores (there is a limit to the size
of the array in Excel), but for your sample data, it seems to work for up to
about 600 units.

 

[Glenn]

Well, it doesn't necessarily need helper columns, although there are
some limits. Assuming your percentages are in A1:A15 (and A16:A73 are
empty), and your number of units is in C1, put the following array
formula (commit with CTRL+SHIFT+ENTER) in B1 and copy down to B15:

=SUM(--(($A1/ROW(INDIRECT("$2:$74")))+
(ROW(INDIRECT("$2:$74"))/POWER(4,16))>=
LARGE(MMULT(($A$1:$A$74)+0,
TRANSPOSE(1/ROW(INDIRECT("$2:$74")))),C$1)))

Might not work for you due to the number of stores (there is a limit to
the size of the array in Excel), but for your sample data, it seems to
work for up to about 600 units.

Fixed the ROW() references inside the INDIRECT():

=SUM(--(($A1/ROW(INDIRECT("1:73")))+
(ROW(INDIRECT("1:73"))/POWER(4,16))>=
LARGE(MMULT(($A$1:$A$74)+0,
TRANSPOSE(1/ROW(INDIRECT("1:73")))),C$1)))

 

[Glenn]

And one more minor fix:

=SUM(--(($A1/ROW(INDIRECT("1:73")))+
(ROW(INDIRECT("1:73"))/POWER(4,16))>=
LARGE(MMULT(($A$1:$A$73)+0,
TRANSPOSE(1/ROW(INDIRECT("1:73")))),C$1)))