Donate SIGN UP

Excel Random Numbers

Avatar Image
5freemen | 20:20 Mon 06th Aug 2007 | Computers
2 Answers
I know most questions on AB are hardware issues, but I guess this is the best place for an Excel logic problem. I want to generate thousands of sets of 4 random whole numbers between 1 and 20. I know I can do this with "=roundup(rand()*20,0)". However, I want to generate the next number in the set without replacement (repitition of the previous numbers).

I thought about generating the next number between 0 and 19 and then having an extra criterion of if it equalled the first number, set it to 20. This works fine, but if I then extend it to the third number and generate a number between 1 and 18 this breaks down as I need to select which number to set it to if there's a repeat, but this could be a repeat of the second number. I guess I could have a string of "if" statements checking each one, but I still can't get it to work. The thought of the 4th non-repeated from the set is driving me batty.

There must be an easier way - I even thought Microsoft would have a standard function - but I can't find it. If you have solved this problem easily please let me know.
Gravatar

Answers

1 to 2 of 2rss feed

Best Answer

No best answer has yet been selected by 5freemen. Once a best answer has been selected, it will be shown here.

For more on marking an answer as the "Best Answer", please visit our FAQ.
Use this support fourm from microsoft:
http://support.microsoft.com/kb/86523
I'm not quite sure that this is possible given your criteria ... by definition "random" implies without pattern ... yet you stipulate non repeating - which is after all a pattern... truly random would by definition predict repeating numbers.

in it's simplest form using a nest of if/or statements can produce a blank cell .... but then what about repeated numbers which of course are also bound to occur?

http://www.random.org/
may give you more to think about

1 to 2 of 2rss feed

Do you know the answer?

Excel Random Numbers

Answer Question >>