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*C21, ROUND($G$1*C2, 0), 1)),0)

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

D15: =G1-SUM(D1:D14)

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

Jim Thomlinson


"Jim Thomlinson" wrote:

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.
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

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$1:D1),ROUND($G$1*C2,0)),0)
D:15: =G1-SUM(D1:D14)

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

Jim Thomlinson


"Bob Phillips" wrote:

Logic error in formula from D2 down. Try

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

--
__________________________________
HTH

Bob

"Jim Thomlinson" wrote in 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?
--
HTH...

Jim Thomlinson


"Bob Phillips" wrote:

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$1:D1))*C2,1),0)

copy D2 down

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


--
__________________________________
HTH

Bob

"Jim Thomlinson" wrote in
message
...
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.
--
TIA...

Jim Thomlinson