|
Allocating Intergers based on Relative Percentage
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 |
Allocating Intergers based on Relative Percentage
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 |
Allocating Intergers based on Relative Percentage
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 |
Allocating Intergers based on Relative Percentage
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 |
Allocating Intergers based on Relative Percentage
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 |
Allocating Intergers based on Relative Percentage
"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 |
Allocating Intergers based on Relative Percentage
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 |
Allocating Intergers based on Relative Percentage
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 |
Allocating Intergers based on Relative Percentage
"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 |
Allocating Intergers based on Relative Percentage
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 |
Allocating Intergers based on Relative Percentage
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 |
Allocating Intergers based on Relative Percentage
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$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 |
Allocating Intergers based on Relative Percentage
Hello Jim,
I suggest to apply the d'Hondt method: http://sulprobil.com/html/d_hondt_method.html Regards, Bernd |
Allocating Intergers based on Relative Percentage
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 |
Allocating Intergers based on Relative Percentage
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$1:D1))*C2/SUM(C2:$C$15),0)), $G$1-SUM($D$1:D1)) 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 ----- "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$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 |
Allocating Intergers based on Relative Percentage
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 |
Allocating Intergers based on Relative Percentage
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$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 |
Allocating Intergers based on Relative Percentage
"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. |
Allocating Intergers based on Relative Percentage
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))) |
Allocating Intergers based on Relative Percentage
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))) |
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))) |
Allocating Intergers based on Relative Percentage
"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$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 |
Allocating Intergers based on Relative Percentage
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$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 |
| All times are GMT +1. The time now is 05:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com