Random Percentages That Can't Exceed 100%

[HGLIII]

Good Afternoon,

I have been struggling with this problem for a few months and several people have kindly attempted to help through the forums, but I am hoping I can finally solve my problem definitively.

Is it possible to create a formula that will generate a random number between 0.00% (floor) and a ceiling (this is a number I would like to control independently in a different cell, to test many different iterations) - assume 2.50%. However, I need to apply this formula over 101 cells, vertically, and the total must always equal 100.00%.

For example:

1. 0.25% (first random number)
2. 1.32%
3. 0.88%
4. 2.01%
5. 0.01%
6. 1.36%

(etc., with all other 94 cells following similar pattern)

101. 0.57% (last random number)

Total = 100.00%

A problem that has cropped up before is that the first 15 or 20 "consume" the distribution, so that the further down the cells you go, the smaller the number is or often just 0%. So I need to distribution to truly be random, as exemplified above.

Any help would be GREATLY appreciated!

Many thanks,

HGL

 

[Logit]

.

[TABLE="width: 512"]
<colgroup><col span="8"></colgroup><tbody>[TR]
[TD="colspan: 6"]In Col A, from A1:A100 enter this formula : =RAND()[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]In B1 enter this formula : =A1/SUM(A:A)*1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 7"]Then, click on B1 and drag the formula down the column to B100

Format Col B as PERCENTAGE / 2 decimal places[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]Press F9 for new set of numbers to be displayed.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[HGLIII]

Fantastic, thanks Logit!

Is there any way to change the maximum number generated (i.e., the "ceiling" on the distribution) to a unique, pre-determined number? For example, 2.5%, 3%, 5%, etc.? It seems that the natural typical min/max for what you created is between 0% and 2%.

 

[Logit]

.
If any cell value were above 3% or above I believe some of the other cell values were be equal to 0%
to accommodate for the higher numbers.

If you are OK with the total of the 100 cells equaling 1,000 you could use this formula, copied down Col B,
as it would create larger numbers for some of the cells :

=A1/SUM(A:A)*10

 

[Eric W]

This topic has been discussed several times here. Here are a couple links:

https://www.mrexcel.com/forum/excel...rs-total-set-value.html?highlight=randbetween

https://www.mrexcel.com/forum/excel-questions/1041380-assign-random-probability-chart-elements.html

There are formula versions, and VBA versions. The general consensus is that such a list can be generated, but by forcing too many constraints on it (must sum up to a given value, must have upper and lower limits, must be integer (in some versions), you're really taking away the randomness. If you take a formula version, as you've seen, the numbers calculated first usually end up exceeding the limit, leaving nothing left for the rest. So you'd have to recalculate the formulas (F9 for formulas, or rerun the macro) many times to get a distribution you like. And how random is that? Of the methods provided in the other links, I like shg's "cutting" technique, although it doesn't have an upper limit on the size of the values. But you might get something useful from those links.

 

[HGLIII]

Eric, thank you for the reply and references!

Logit,

Having some values equal 3% and others equal 0% would be perfectly fine. That's actually what I'm trying to accomplish (a replication of a situation where the composition distribution is very unequal and random, with large variation in the cell-to-cell comparison).

Would there actually be a way to drive up the maximum distribution as discussed?

(Unfortunately I can't use the 1,000% approach because I need the total to equal 100%)