Weighted RANDBETWEEN() ?

[avsrock90]

Hi All,

In cells A1:A11 I have a list of probabilities associated with numbers (1-11) that are listed in cells B1:B11. A1:A11 sum to 100%.

I am wondering if there is a way to return 1,2,3,4...,9,10,11 with the probabilities I specify rather than doing =randbetween(1,11) and having it be roughly 1/11th of the time for each.

Basically in another sheet I want to be able to to have a number 1-11 appear with the probability I define. I guess I'm not really looking for a random number, but I'm looking for a function that will return a number with probabilities I specify. Any ideas?

Thanks for any help!

 

[joeu2004]

=(RAND() >= ($B$3-COUNTIF($A$1:A1,0)) / ($B$2-ROWS($A$1:A1)+1))*1

That should be RAND()>..., not RAND()>=... .