Generating permanent random
numbers in Excel for Statistical Analysis


Excel 2007  Excel 2003  Macros Excel 2007  Macros Excel 2003  Excel 2010 



A
question from
a
website visitor: I'm using the Random Function in MS Excel to generate a thousand numbers but each time I press F9 all the random numbers change and my results based on functions change. Is it possible to generate, let's say, a thousand unique whole numbers between 1 and 6000 in one single column which don't change so that I can perform statistical analysis on them. Typing large amount of numbers into an Excel worksheet is a boring and unproductive method for doing simulation studies. You need an automated method. As we showed earlier you can generate random numbers by using the Rand() or RANDBETWEEN() excel functions to automatically generate random numbers. One of the main disadvantage of generating numbers in this fashion is the fact that they keep on changing when you press F9 from the keyboard. Now when you have associated formulas with these numbers the results also change and that may not be exactly what you wanted to do! The Excel VBA code or macro for the problem is given below: Sub RandomNumbers() For i = 1 To 1000 back: ranNum = Application.RoundUp(Rnd() * 6000, 0) If Application.CountIf(Range("B:B"), ranNum) > 0 Then GoTo back Else Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = ranNum End If Next End Sub Watch the training video below to learn how to generate unique and permanent numbers for simulations and statistical analysis: 

Excel 2007  Excel 2003  Macros Excel 2007  Macros Excel 2003  Excel 2010 
