Random numbers but each number not eqully random.

1) Add a column =sum(B$1:B1) (note relative and absolute references). You are creating a lookup table to use with the built in RAND() function.
2) Use lookup function INDEX($A$1:$A$8,MATCH(RAND(),$C$1:$C$8,1)) Note that it is using the "binary" search on an ascending list option for the third argument of the MATCH() function.

Hi,

I think you mean that you want the numbers to be DISTRIBUTED randomly. By definition since you are specifiying the number of times the numbers 0-7 should appear they are NOT random numbers.

So the approach I'd adopt is list the numbers
0 - 37 times
1 - 320 times
2 - 1172 times
... etc
7 - 152 times

now alongside the 9999 numbers listed add the formula =RAND() in the adjacent column.

Now sort the two columns using the random number column as the key.

Originally Posted by Richard Buttrey

Hi,

I think you mean that you want the numbers to be DISTRIBUTED randomly. By definition since you are specifiying the number of times the numbers 0-7 should appear they are NOT random numbers.

So the approach I'd adopt is list the numbers
0 - 37 times
1 - 320 times
2 - 1172 times
... etc
7 - 152 times

now alongside the 9999 numbers listed add the formula =RAND() in the adjacent column.

Now sort the two columns using the random number column as the key.

You are likely right and I actually did that but I had trouble's adding my formulas in above worksheet. Maybe that is the way I need to go but if I could random the 0-7 as I mentioned above my formula's would work as is.

This was my attempt at this method.
=Some reason I cannot post my formula ?

As soon as you introduce controls like this, it's not longer an exercise in random number generation. It's now an exercise in randomizing a preset list of numbers.

Please Login or Register  to view this content.

So you create a list with numbers in column A repeated the number of times you want calculated in column C, all in one column.
Then a RAND() formula in the next column to generate a truly random number to associate with your preset list.

Finally, in a new column, you extract the preset numbers in the order of the adjacent RANDOM numbers. (slow)
or....
Sort the two columns using the RAND column as the sort key. Each time you do this your main set of preset numbers will be in a different order. Very fast.

Attached is an example.

You need to enter 280 number 7s in the list. As well as 16 zeros, 172 1s, 774 2s...etc

Originally Posted by richhhh

That make total sense. Thank you !

Glad to have helped, and stating the obvious, if you change the %s again you'll need to also change the list to reflect the changed number of 0-7s

I notice from your file that your probabilities are being generated by the binomdist() function. I suspect that one can use the binom.inv() function to generate these random numbers in the same way that we use the norminv() function to generate random numbers that follow a normal distribution. binom.inv() is not available in my version of Excel, so I cannot test it. If you are interested, and you generally expect to know what distribution you are planning to use to generate your random numbers, it might be worth the research to learn how to use the inverse distribution functions to generate these random numbers:
http://www.exceluser.com/formulas/statsnormal.htm talks about the normal distribution, eventually getting to the part about using norm.inv() to generate random numbers along a normal distribution.
http://www.mathwave.com/articles/ran...sheets-p2.html talks about using inverse functions, some caveats and pitfalls, and pushes their product that they claim is superior at generating random numbers for various distributions.
http://www.real-statistics.com/sampl...ns/simulation/ further discussion about using Excel's built in tools for generating random numbers along different distributions.
There are almost certainly others out there, if you put the question to your favorite internet search engine.