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!

 

[Jerry Sullivan]

Here's one way to do this with worksheet formulas. A similar approach could be applied with VBA...

Excel Workbook
	A	B	C
1	Item 01	0.140664	3.19%
2	Item 02	0.76306	17.30%
3	Item 03	0.860855	19.51%
4	Item 04	0.130917	2.97%
5	Item 05	0.159946	3.63%
6	Item 06	0.586339	13.29%
7	Item 07	0.079925	1.81%
8	Item 08	0.990514	22.45%
9	Item 09	0.674908	15.30%
10	Item 10	0.024377	0.55%
11			100.00%
Sheet

The RAND() values recalculate any time there is a calculation on the sheet. You can copy-paste as values if you need to lock in a set of values.

 

[asking]

Hi vanclute,

You could also do this.

In simple terms you need to make a bookies book of a set of numbers to 100%
This is how ~ In a spare column (if your numbers are in column A) type in ~ =1/A1 and fill down the required amount of cells.
At the bottom of those cells in the next cell down, double click the auto sum icon in the menu, now copy the answer, now select all your original numbers.
Now in the menu click paste special, click values, click multiply.
Done.

Cheers

 

[sdruley]

http://screencast.com/t/AsQXV4Ly

In column D plug in =RAND() and copy down
In column E plug in =D6*$E$5 and copy down
In cell E5 put in =RANDBETWEEN(8,12)
In cell E27 type in =SUM(E6:E25)
In a module paste:

Sub OPTIMIZE()Do
Calculate
If Range("E27").Value >= 99.9 And _
Range("E27").Value <= 100.1 Then Stop
Loop
End Sub

This method is pure randomness and represents a rectangular distribution similar to the lottery. Dividing each element by the probability sum distorts the true randomness.

 

[Jerry Sullivan]

Hi Stephen,

Can you explain what you mean by...
"This method is pure randomness and represents a rectangular distribution similar to the lottery"

With that method, it would appear that no value could ever be greater than 12, which seems not so random.

 

[mirabeau]

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!

Hi,

Would you like to try out this code on a blank sheet and see if it's something like what you want?

Sub sumrandom()
Dim s#
With Range("A1:A20")
    .Formula = "=rand()"
    .Value = .Value
    s = Application.Sum(.Cells)
    .Cells = Evaluate(.Address & "/" & s)
    .NumberFormat = "0.0%"
End With
End Sub

 

[sdruley]

Jerry,


Thank you for asking. Your solution, from a practical standpoint, is probably the best one submitted and it is certainly the fastest.
I believe we both started out by asserting randomness in the first column, which satisfies the requirement of randomness within each cell. Now we are faced with the task of randomly coercing the sum of the cells to be 100%. I chose to apply a random multiplier to preserve the randomness from cell to cell. Randomness can be created within any finite space as long as the objective can be met. "12" was chosen to keep the run time manageable.
Again, your solution is fine.

Steve

 

[mirabeau]

...

Thank you for asking. Your solution, from a practical standpoint, is probably the best one submitted and it is certainly the fastest.

...
Steve

Steve,

What you think is "probably the best" is probably the best for you.

But your claim about "certainly the fastest", made without any supporting evidence (did you do any testing?), is I think unhelpful, unfair to other contributors and likely to badly mislead any other readers of this thread, for example those who might arrive here through Google.

Just doing a timing test indicated that the solution which you refer to as fastest took about ten times as long on a 10,000 cell range as did the solution in post #6 of this thread. For a 50,000 range the difference became much greater. Would you like to post your own test results that either verify or refute this?

("The quality of thinking is measuredby remoteness to conformance"
Stephen Druley)

 

[shg]

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)

 

[Jerry Sullivan]

Hi shg,

Thanks for sharing that technique - I can see how that formula is more analagous to cutting a piece of string.

I'm not really following why the method I suggested gives a "very peculiar distribution". Can you help me understand why my approach yields different patterns of distribution?

I'm aware of your mathematics expertise, so I don't doubt that you are correct- but I haven't grasped why these two approaches would result in different distributions.