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.

 

[ilcaa]

thank Angel, your right, not sure why i did that but it works fine changing it to 1

 

[tommyxps]

Thank you for the solution, however do you know how to eliminate all the duplicates in the results?
For example, I dragged F2 into a list to get 60 random results and found many duplicates probably because the higher percent, however I would like to get 60 unique values from column A(assuming A has more than 60 values)

My silly way of doing it is deleting the value generated from original list (I make it blank if the value can be found in another aggregated list with all results ), then re run the F2 formula to generate a new value from whatever left, but the results contain many blanks now.

I am now stuck without any clue.

Please help!

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

 

[LiangY]

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:

	A	B	C	D	E	F
1	Option	Assigned Percent	Cumulative Percent
2	A	10%	10%		Randome Value =	D
3	B	25%	35%
4	C	13%	48%
5	D	35%	83%
6	E	17%	100%

<tbody>
</tbody>

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

Hello Angel, could you explain a little bit the principles using "<="&Rand(), what does "<="& stands for? Thank you!!!

 

[Joe4]

Hello Angel, could you explain a little bit the principles using "<="&Rand(), what does "<="& stands for? Thank you!!!

Welcome to the Board!

Note that the danger with posting to old threads is that the users involved on the thread may not be active on this board anymore, so you may not get a response (Angel has not logged on to this board in over a year). Typically, it is usually best to post your own brand new thread with your question, than it is to post to old threads that have't had activity in a few years. You can always include links to other threads, if that is helpful in describing your question.

BTW, "<=" is the mathematical sign meaning "less than or equal to".