Generating Numeric Testing Data

Written by Allen Wyatt (last updated August 12, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


3

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

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Cannot Convert a Document File to a Readable Document

Open a Word document file, and you can start to make edits and changes to your heart's content. But what happens if the ...

Discover More

Arranging Document Windows

When you have multiple documents open at the same time, you need a way to control how those document windows appear on ...

Discover More

Reapplying Styles Seamlessly

Styles are a very powerful feature in Word, as they allow you to consistently apply complex formatting throughout a ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Throwing Out the Lowest Score

Want to add up a bunch of scores, without including the lowest one in the bunch? You can make a small change to your ...

Discover More

Finding Circular References

If you have circular references in a workbook, you may see an error message appear when you first open that workbook. If ...

Discover More

Removing Duplicates at a Reduced Precision Level

The precision of numeric values you display in a worksheet can be less than what is actually maintained by Excel. This ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine minus 3?

2023-08-17 14:56:07

J. Woolley

My previous comment below discussed Excel 365's TAKE function, which is NOT included in Excel 2021 or earlier. The following function in My Excel Toolbox provides capability similar to the TAKE function as well as Excel 365's DROP and EXPAND functions:
=Resize(RangeArray, [Rows], [Cols], [PadVal])
where RangeArray is a contiguous cell range or array constant or result of an array function. Rows is the number of rows to pick from the start, or ABS(Rows) from the end if < 0; default is 0 for all rows. Cols is the number of columns to pick from the start, or ABS(Cols) from the end if < 0; default is 0 for all columns. If ABS(Rows) or ABS(Cols) expands RangeArray, then added rows/columns are optionally set to PadVal.
Here is the formula from my previous comment using Resize instead of TAKE:
=SORT(Resize(UNIQUE(ROUND(RANDARRAY(10123,1,100.01,99999.99,FALSE),2)),10000))
See https://sites.google.com/view/MyExcelToolbox


2023-08-16 02:19:50

Michael van der Riet

This works fine as long as you realise that this generates pseudo-random sets and is unsuitable for heavy-duty apps. Best practice is to download true randoms. There are several pages that offer this, usually at no charge up to about a hundred thousand numbers. To reduce the hassle factor when I was calculating the odds on card games, I downloaded one large set that I could use many times, and used a pseudo-random to determine the offset into the set.


2023-08-13 11:52:30

J. Woolley

The Tip says, "...if you use the UNIQUE function, the list of values will be reduced by the number of duplicate values in the original list." This can be resolved by generating a larger list (like 10,123), then using Excel 365's TAKE function:
=SORT(TAKE(UNIQUE(ROUND(RANDARRAY(10123,1,100.01,99999.99,FALSE),2)),10000))
Note: The TAKE function is NOT included in Excel 2021 or earlier.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.