Evenly distribute 100% across a selection of items.

[vanclute]

I would imagine this has been asked many times before as it seems like a somewhat common thing to need to do, but I'll be ****ed if I can find it.

So I have a list of items... could be any number but let's say it's 20 cels for now. I want to randomly assign a percentage to each, such that the total adds up to 100%.

How to accomplish said task in Excel? Anyone have any ideas?

Thanks!

 

[shg]

See the workbook at https://www.box.com/s/4c328bc6905f3de25cab

The first tab is a Monte Carlo construction (using 1M variates) of the PDF and CDF for random lengths via summing and normalizing five random numbers, and the PDF and CDF via string cutting. (Press New data to generate another set). Note the knee in the sum-and-normalize PDF; it's some kind of rational function, and totally unlike the string-cutting PDF.

The second tab calculates the PDF and CDF for string cutting using the beta distribution. Note how well it correlates with the first tab if you set N=5 (using the spin button).

 

[shg]

I have a UDF that generates random numbers that sum to a given value. I'll post it if you're interested.

 

[sdruley]

Jerry,

I was trying to say that YOUR method was good and certainly much faster than mine. Are you addressing the above message to me or shg?
Since the distribution you asked to be created is rectangular rather than gaussian, then the solutions presented are fine. A rectangular distribution is defined where each cell has the same probability of occurence. If an infinite number of samplings were taken you would see that they each cell would contribute equally to the 100%. As long as this equality of probability exists in the distribution, and we recognize that this distribution is rectangular in nature, then there is nothing peculiar about your solution.

 

[shg]

Posts 11 & 12 were directed to Jerry -- sorry for any confusion.

 

[Jerry Sullivan]

shg, Thank you for providing the link to the workbook that compares those two methods.

It might take me a little time to assimilate it, so I wanted to take a moment to acknowlege my appreciation for your help.

 

[shg]

That's very thoughtful, Jerry, you're welcome.

 

[vanclute]

OK, first off I just have to say to you all...

:O :shock:

We don't appear to have a wide array of emoticons around here so suffice it to say... you have all blown me right out of the water with the depth of your replies. Wow.

Frankly I'm not a "math pro" so had never even considered the types of distributions, true randomness, or anything of the sort. I just wanted a quick way to "randomly" set up percentages for a list of 20 items and have them total 100%, vs me just "eyeballing" a bunch of values in increments of 5 or 10. I had no idea there were so many different possible solutions and ways of even thinking about the problem!

I'm still digesting the responses but I'm confident the answer that I need is in here. Just need to get my head around them.

So thanks again everyone... I think this may be the most thorough, insightful thread I've ever had the pleasure to start.

 

[vanclute]

Generating numbers in that fashion gives a very peculiar distribution as a result of using 10 random numbers when only nine are needed. If you take a piece of string of the desired length and want 10 pieces of random length, you only need 9 cuts. The result is a beta distribution of lengths.

       A- --B--- -----C------ D ---E---
   1    1      0 B1: Input        1.58%
   2    2 0.8539 B2: =RAND()     18.68%
   3    3 0.6558 B3: =RAND()      3.53%
   4    4 0.5999 B4: =RAND()      3.72%
   5    5 0.0158 B5: =RAND()     32.49%
   6    6 0.2379 B6: =RAND()      1.09%
   7    7 0.2751 B7: =RAND()      4.50%
   8    8 0.2025 B8: =RAND()     19.81%
   9    9 0.6108 B9: =RAND()      3.20%
  10   10 0.8859 B10: =RAND()    11.41%
  11   11      1 B11: Input            
  12                                   
  13                            100.00%

The formula in E1 and copied down is

=SMALL($B$1:$B$11, A1+1) - SMALL($B$1:$B$11, A1)

This is a nice elegant solution that is getting me what I need, with one small exception. I need to ensure that no item has less than 1% assigned to it. How would you go about setting such a floor?

 

[shg]

=(small($b$1:$b$11, a1+1) - small($b$1:$b$11, a1))*(1-10*1%) + 1%

 

[vanclute]

=(small($b$1:$b$11, a1+1) - small($b$1:$b$11, a1))*(1-10*1%) + 1%

Ahh excellent. Thank you! Obviously that will only work for lists of fewer than 100 items but that's what I'm working with so... perfect!