random generated numbers!

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!
 
B

Biff

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
 
K

Ken Wright

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.
 
K

Ken Wright

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))
 
Top