Generating Numeric Testing Data

Tom needs to generate some numeric testing data that consists of random numbers that are greater than or equal to 100.01 and less than or equal to 99,999.99. His dataset should consist of at least 10,000 numbers, and they need to be sorted in ascending order. Tom knows he can do this using a bunch of individual steps and helper columns, but he wonders if there is a way to generate the numbers using just a single formula.

The traditional way of doing this involves using helper columns or multiple steps. All you would need to do is to place this formula into a cell:

=RANDBETWEEN(10001,9999999)/100

Copy it down for 10,000 cells and you'll have your random numbers, to two decimal places. You can then copy all the values and use paste special to convert them to values. Finally, you can sort the values into ascending order.

If you are using the newest versions of Excel, you could remove one of the steps (the need to sort) by using this formula:

=SORT(RANDARRAY(10000,1,100.01,99999.99,FALSE))

Provided there is nothing in the 10,000 cells below where you insert this formula, you'll end up with a sorted list of values in the desired range. If you want to limit the values to two decimal places, you could modify the formula in this manner:

=SORT(ROUND(RANDARRAY(10000,1,100.01,99999.99,FALSE),2))

Finally, you could ensure that no values are repeated (something that is not possible by any of the formulas so far presented) by expanding the formula just a bit:

=SORT(UNIQUE(ROUND(RANDARRAY(10000,1,100.01,99999.99,FALSE),2)))

Be aware, though, that if you use the UNIQUE function, the list of values will be reduced by the number of duplicate values in the original list. Thus, you may end up with a list of only 9,994 or 9,998 values instead of 10,000. For that reason, you may want to increase the 10000 parameter in the formula to a slightly larger value.

Regardless of the formula you choose to use, you'll quickly find out that the results are volatile, meaning that they update every time you do something else in your worksheet. You'll want to copy the values and use paste special to make the values static.

If you have to generate lists of values regularly, you may be interested in a macro to do the work for you. The following macro adds a worksheet and then, in column A, places 10,000 random values using the same RANDBETWEEN formula introduced earlier. It then sorts the values in column A so that you end up with a sorted list.

Sub RandNums()
    Dim Rownum As Integer

    For Rownum = 1 To 10000
        Cells(Rownum, 1) = Application.WorksheetFunction.RandBetween(10001, 9999999)/100
    Next Rownum

    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A1:A10000"), SortOn:=xlSortOnValues, _
          Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A1:A10000")
        .Apply
    End With
End Sub

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (4273) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.