Allocating Intergers based on Relative Percentage

Interesting but once again it is full of volatile functions and will dim the
lights on the ol' death star.
--
HTH...

Jim Thomlinson


"Glenn" wrote:

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

"Bob Phillips" wrote:
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)

I don't believe that works when the number of units is 6, what Jim started
with, or less.

In fact, I believe it does not work for a wide range of numbers.
Considering just the numbers = 100: 2-6, 16-18, 26, 27, 37-40, 42, 43, 55,
65-67, 79-82, and 88-92.


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

"Bob Phillips" wrote in message
...
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)


--
__________________________________
HTH

Bob

"Jim Thomlinson" wrote in
message ...
Typo in D1...

D1: D1: =MAX(MIN(CEILING($G$1*C1,1), IF($G$1*C11, 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...
--
TIA...

Jim Thomlinson


"Jim Thomlinson" wrote:

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

D15: =G1-SUM(D114)

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

Jim Thomlinson


"Bob Phillips" wrote:

Logic error in formula from D2 down. Try

=MAX(MIN($G$1-SUM($D$11),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$11))*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

PS....

By "not work", I mean that it fails to meet Jim's criteria, specifically:
"all units must be allocated. No more and no less".


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

"JoeU2004" wrote in message
...
"Bob Phillips" wrote:
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)

I don't believe that works when the number of units is 6, what Jim started
with, or less.

In fact, I believe it does not work for a wide range of numbers.
Considering just the numbers = 100: 2-6, 16-18, 26, 27, 37-40, 42, 43,
55, 65-67, 79-82, and 88-92.


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

"Bob Phillips" wrote in message
...
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)


--
__________________________________
HTH

Bob

"Jim Thomlinson" wrote in
message ...
Typo in D1...

D1: D1: =MAX(MIN(CEILING($G$1*C1,1), IF($G$1*C11, 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...
--
TIA...

Jim Thomlinson


"Jim Thomlinson" wrote:

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

D15: =G1-SUM(D114)

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

Jim Thomlinson


"Bob Phillips" wrote:

Logic error in formula from D2 down. Try

=MAX(MIN($G$1-SUM($D$11),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$11))*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