LinkBack URL
About LinkBacks
Hi,
I need to generate 8 (random) numbers which should range between 0 to 80 and the sum of those generated 8 numbers should be 80.?
How can I achieve this.
Thanks,
sakkthi
Hi,
I need to generate 8 (random) numbers which should range between 0 to 80 and the sum of those generated 8 numbers should be 80.?
How can I achieve this.
Thanks,
sakkthi
in A1: =rand()*80
in A2: =rand()*(80-A1)
in A3: =rand()*(80-sum($A$1:A2))
Copy A3 down to A7
in A8: 80-sum(A1:A7)
Hi Yudlugar - Can you possibly explain the maths behind that?
Not really, for each cell in turn I generated a random number between 1 and 80 minus the total of the previous numbers. For the last cell I used the difference between 80 and the previous 7 numbers.
Cheers, that helped - Can now see whats happening.
You could try this:
In A2 put this formula and copy down to A9
=RAND()
Now in B2 use this formula copied to B9
=A2/SUM(A$2:A$9)*80
The values in B2:B9 now constitute your 8 random numbers
Audere est facere
Yes, I think that this latter is a 'better' way of generating a random list, as values are not dependent on the preceding entry in the generated list.
The solution proposed by yudlugar effectively gives 80 choices to the first random entry; the choice for each subsequent value is then conditional on (restricted by?) the previous values. Hence, a tendency for zeroes towards the end.
Will have to consider it a bit more, but perhaps this in effect means that the solution by yudlugar is not, strictly speaking, correct.
Regards
XOR, agreed, the solution I gave I would imagine would give a higher probability of certain combinations appearing. However, I'm not sure if this is a function of the problem or the solution. I would think the constraint of the numbers being between 0 and 80 with a limit of the total being 80 would mean there is a tendency towards zeros regardless of the method.
DaddyLongLegs solution doesn't seem to work for me. I get big numbers as rand returns between 0 and 1, so I'm dividing each number by the sum of 8 numbers <1 then multiplying by 80. The total is much bigger than 80.
His solution works fine for me.
Re the other points, I tend to agree with you instinctively, though I'm not sure yet, which is why I said I need to think about this a bit more!
Regards
I got DaddyLongLegs solution working, I ran a couple of thousand iterations and plotted a histogram. I got the following:
number yud DLL
0-5 64% 24%
5-10 9% 27%
10-15 5% 29%
15-20 4% 16%
20-25 3% 3%
25-30 2% 1%
30-35 2% 0%
35-40 2% 0%
40-45 1% 0%
45-50 1% 0%
50-55 1% 0%
55-60 1% 0%
60-65 1% 0%
65-70 1% 0%
70-75 1% 0%
75-80 1% 0%
I'm not sure which I would call correct, I think for a problem like this you need an additional property, like a shape function I guess to define the split between different numbers.
edit: the 0 percentages are not rounded down, there were no instances of these numbers.
Last edited by ragulduy; 10-24-2013 at 07:18 AM.
To encourage some higher numbers you could introduce an exponential element, e.g. change my B2 formula to the following:
=A2^2/SUMPRODUCT(A$2:A$9^2)*80
Interesting analysis, yudlugar. The problem of generating 'randomness' here is obviously not as staightforward as the two proposed solutions.
Still thinking...
True, I think you could also discourage lower numbers in mine by changing A1=rand()*80 to something like A1=rand()*60. Those changes would give:
0-5 60.23% 39.34%
5-10 10.03% 17.81%
10-15 6.25% 14.70%
15-20 4.41% 13.15%
20-25 3.74% 8.56%
25-30 3.14% 3.74%
30-35 2.56% 1.54%
35-40 2.31% 0.68%
40-45 2.06% 0.27%
45-50 1.69% 0.14%
50-55 1.54% 0.02%
55-60 1.38% 0.03%
60-65 0.29% 0.01%
65-70 0.19% 0.00%
70-75 0.16% 0.00%
75-80 0.03% 0.00%
It looks as though the more higher numbers you want, the more 0 numbers you get as well. If you want mid-range numbers you need to have a low number of high numbers. Although I maybe need a bigger samples size to get accurate percentages.
Maybe:
in A2:A9: = rand()
in B2:B9: =A2^C$2/SUMPRODUCT(A$2:A$9^C$2)*80
in C2: =Rand()*5
This would give:
37.94%
19.01%
19.94%
9.94%
5.36%
3.21%
1.88%
1.06%
0.63%
0.40%
0.29%
0.13%
0.11%
0.08%
0.02%
0.01%
As before though, the 5 in C2 would still skew the shape of the distribution. I don't think it is possible to have this as truly random without a property of the formula defining how the distribution falls.
Agreed. Any solution which pre-incorporates the desired total in its methodology is bound to be subject to bias.
I'm afraid the only true way to ensure randomness amongst these 8 choices would be to enter =RANDBETWEEN(0,80) into 8 cells and iterate enough times until the total for those 8 cells is precisely 80, though, depending on the number of decimal places, we could be talking a long time here.
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks