EXCEL RANDOM NUMBERS 2

[elfa]

Hello,

If I have seven numbers with probability percentages (see below) can I get excel to show me what would what numbers would be displayed after a number of instances.

ie. after 10 instances excel would return 1213467743

Obviously the more instances that are chosen the closer you get to the percentages below.

1 = 1%
2 = 2%
3 = 3%
4 = 7%
5 = 15%
6 = 23%
7 = 49%


Thanks Elfa

 

[xlbo]

There is no point showing numbers as you are working with probability - ity could take 10 goes to get those numbers or 10000 goes. what is it that you actually want to do ??
Choose random numbers based on the statistical probability marked next to them ?? You would need code (and probably quite complex code) for that

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[DrBowes]

Not sure if I have understood but this may be the kind of thing you are after:

Put these values in A1:B7 (cumulative percentage values from above)

0 1
0.01 2
0.03 3
0.06 4
0.13 5
0.28 6
0.51 7

Then in A9:A18 put the formula =Rand()

In B9 put the formula =VLOOKUP(A10,$A$1:$B$7,2,TRUE). Drag this down to B18.

Finally in B20 put the formula

=CONCATENATE(B9,B10,B11,B12,B13,B14,B15,B16,B17,B18)

Pressing on F9 will recalculate the random numbers and give you a new value.

Change the number of instances by dragging down the RAND and VLOOKUP formulas and ading to your concatenated formula.

 

[DrBowes]

Sorry change the A10 to A9 in the formula in B9 - sorry....

 

[xlbo]

Nice job DrBowes


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[elfa]

Cheers Doc.

Spot on.

Regards
Elfa.

 

[DrBowes]

Thanks for the star.

Here is code to do the same, it makes choosing the number of instances easier.

Sub MakeRandomString()
For i = 1 To InputBox("Enter number of instances")
Randomize
Select Case Rnd
Case Is >= 0.51
y = 7
Case Is >= 0.28
y = 6
Case Is >= 0.13
y = 5
Case Is >= 0.06
y = 4
Case Is >= 0.3
y = 3
Case Is >= 0.51
y = 2
Case Else
y = 1
End Select
x = x & y
Next i
MsgBox x
End Sub

 

[DeLaMartre]

Here's to the Doc!

Good stuff (and another star).

Thanks for your expertise,

-Bob in California