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

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?

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jim Thomlinson" wrote:

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

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

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

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

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

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

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

"Jim Thomlinson" wrote:
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.

Bob wrote:
D2: =MAX(CEILING(($G$1-SUM($D$1:D1))*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 -----

"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

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

Jim Thomlinson


"JoeU2004" wrote:

"Jim Thomlinson" wrote:
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.

Bob wrote:
D2: =MAX(CEILING(($G$1-SUM($D$1:D1))*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 -----

"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

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

--
__________________________________
HTH

Bob

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

Bob wrote:
D2: =MAX(CEILING(($G$1-SUM($D$1:D1))*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 -----

"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

Hello Jim,

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

Regards,
Bernd

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

Jim Thomlinson


"Bernd P" wrote:

Hello Jim,

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

Regards,
Bernd

"Jim Thomlinson" wrote:
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


"Bernd P" wrote:
Hello Jim,

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

Regards,
Bernd



Jim Thomlinson wrote:
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 wrote:
"Jim Thomlinson" wrote:
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


"Bernd P" wrote:
Hello Jim,

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

Regards,
Bernd



Jim Thomlinson wrote:
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.


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)))

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)))