random generated numbers!

  • Thread starter Thread starter whiteballa14
  • Start date Start date
W

whiteballa14

i forgot how to make random generated numbers. any help please??


also, when having them generated, can i give each number a probability
of showing?

such as, if my random numbers were 1, 2, 3, 4, 5 - could i make the
probability of
1 - 50%
2 - 20%
3 - 20%
4 - 5%
5 - 5%

Is this possible? thanks!
 
Hi,

There are several ways to generate random numbers. It all
depends on which method you use and the criteria you apply
as to determining the probability.

If you have the Analysis ToolPak add-in, goto Tools>Data
Analysis>Random Number Generation.

There are also worksheet functions like RAND() and
RANDBETWEEN(). Note that some of the options available are
VOLITALE meaning that the value returned will change each
and every time the worksheet calculates.

Biff
 
You could always have a list of numbers eg:-

A B
1 1 =RAND()
2 1 =RAND()
3 1 =RAND()
4 1 =RAND()
5 1 =RAND()
6 2 =RAND()
7 2 =RAND()
8 3 =RAND()
9 3 =RAND()
10 4 =RAND()
11 5 =RAND()

ie it comprises of 50% 1s, 20% 2s, 20% 3s, 5% 4s, 5% 5s

Then just use a a formula such as =INDEX(A1:A11,MATCH(LARGE(B1:B11,1),B1:B11,0))

which will give you a random number from that lot with the probability of occurrence being as you
wanted.

If you wanted more than one at a time eg 5, then you could select say F1:F5, and then array enter
a formula like:-

=INDEX($A$1:$A$11,MATCH(LARGE($B$1:$B$11,{1;2;3;4;5}),$B$1:$B$11,0))

If you get #NAs first off, just hit F9 a couple of times to force a recalc and clear them.
 
Doh!!! Just woken up and realised I screwed that right up. You need 20 numbers, 10 of which are
1s, 4 of which are 2s, 4 of which are 3s, 1 of which is a 4 and 1 of which is a 5

Formulas should be:-

=INDEX(A1:A20,MATCH(LARGE(B1:B20,1),B1:B20,0))

=INDEX($A$1:$A$20,MATCH(LARGE($B$1:$B$20,{1;2;3;4;5}),$B$1:$B$20,0))
 
Back
Top