random numbers with assigned probabilities

[yohhh]

<!-- google_ad_section_end --> <!-- google_ad_section_start -->I'm currently having a problem regarding random number generating, as I can't seem to find the answer anywhere. Anyway, my problem is that I am generating 7 random numbers from 1 to 55, however, the probability of each number being chosen is already given. I know that if you use randbetween(1,55) function, that the probability of all numbers being picked out is equal (in this case, each number has 1/55 chance of being picked). My problem is that I am already assigned the probability of occurence for each number (like 5% for 1, 7% for 2, 25% for 3, 2% for 4, and so on...), and the random number generation should be based on that list of probabilities given to me. How do I do that?

Please do forgive me as I am an excel newbie, and I have utterly no idea what I'm supposed to do next.

By the way, I know little of vba, but willing to learn if that's the only way to solve this problem.

 

[GlennUK]

Well, you could have a list of 100 numbers with those ratios, and then choose 7 of them by using randbetween 1 and 100 , 7 times.

 

[mikerickson]

This example of a smaller range could be adapted to your situation.
Find a number from 1 to 4
<table border=1 cellspacing = 0 bgcolor="#ffffff"><tr bgcolor = "#aaaaaa"><td> <td align=center width=85><b>A</b><td align=center width=85><b>B</b><td align=center width=85><b>C</b><td align=center width=85><b>D</b><td align=center width=85><b>E</b><td align=center width=85><b>F</b>
<tr><td align=center bgcolor="#aaaaaa"><b>1</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Number</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">probablitiy of being chosen</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">expanded list of numbers</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">number chosen</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>2</b><td bgcolor="#FFFFFF" > <FONT color="#000000">1</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">20%</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">1</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">4</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>3</b><td bgcolor="#FFFFFF" > <FONT color="#000000">2</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">30%</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">1</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>4</b><td bgcolor="#FFFFFF" > <FONT color="#000000">3</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">40%</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>5</b><td bgcolor="#FFFFFF" > <FONT color="#000000">4</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">10%</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>6</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>7</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">3</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>8</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">3</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>9</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">3</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>10</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">3</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>11</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">4</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>12</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr></table><table border=1 cellspacing = 0 bgcolor="#ddedcc">
<tr><td colspan=3 align="center">Formulas in this range: </tr>
<tr><td align=center>Range with same formula<td align=center>Cell:<td align=center>holds Formula:</tr>
<tr><td><td align=center>F2 <td align = left >=OFFSET($D$2,INT(RAND()*10),0)</tr></table>
The list in column D could have been 20 1s, 30 2's, 40 3's and 10 4's. In which case the Rand would have to return a number 0-99, rather than the 0-9 in the example.

 

[Sal Paradise]

Create a lookup table. Give your cumulative percentages (offset 1 row backwards) for each number in column A. In column B, write the number. Then use the VLOOKUP() function.

For instance:
0%...1
20%...2
60%...3
70%...4

=VLOOKUP(RAND(),A1:B4,2)

 

[yohhh]

Thank you so much for the fast replies. I have chosen the vlookup solution, for the simple fact that I can make tons of combinations right away.

I do have a question for the vlookup solution, though. Why is it that some of the cells come up with #N/A? I entered the cumulative probabilities of all numbers from 1-55 (meaning, I started with 0.025783, all the way to 1.00000), as you've said, and I thought it might be a problem with the way I put in the probabilities.

 

[barry houdini]

You'll get #N/A when RAND() evaluates to < 0.025783

You should start with zero as per Sal's example....and the final number shouldn't be 1 it will be 1-(probability of 55), e.g. if 55 should have a 2% probability then last number (against 55) would be 0.98

 

[AngelEG]

This is an old thread but I just wanted to add another way of doing this.

My particular list consisted of a lot of different variations and creating an expanded list was not very elegant.

So in case someone is looking for an alternate way of getting a value based on assigned probabilities here's how I tackled this.

I added a cumulative percent right next to the assigned percent:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Option[/TD]
[TD]Assigned
Percent[/TD]
[TD]Cumulative
Percent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]10%[/TD]
[TD][/TD]
[TD="align: center"]Randome Value =[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]35%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]C[/TD]
[TD="align: right"]13%[/TD]
[TD="align: right"]48%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]D[/TD]
[TD="align: right"]35%[/TD]
[TD="align: right"]83%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]E[/TD]
[TD="align: right"]17%[/TD]
[TD="align: right"]100%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Formula on C2 is =SUM($B$2:B2)

Formula on F2 is =INDEX(A$2:A$6,COUNTIF(C$2:C$6,"<="&RAND())+1)

So using the cumulative column I count the rows that are less or equal to the random number and then add 1. This makes it work from 0 to .9999 which is the range RAND() works.

Hope it helps someone at some point later.

Angel

 

[ilcaa]

this just helped me, thanks

 

[ilcaa]

i just found some problems...

please see screen shot... i do get what i expect, (~3% vs ~97%) but sometimes i get #REF!

all the cells have same formula. any suggestions to fix? thanks

https://drive.google.com/open?id=0ByTl_9UVF7ukbEpjNGFNLUx0TDA

 

[AngelEG]

ilcaa,

The cummulative must add up to 1 on your last entry instead of .99

Not sure how you got .99 instead of 1 since you are adding .03+.97, maybe there are some decimals not showing.

The error comes from the values generated by rand() between .99 and 1

Hope it helps.

Angel