Food & Drink0 min ago
Excel Random Numbers
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.
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.
Answers
1 to 2 of 2
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.
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
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 2
