Dummy Data – How to use the Random Functions

Dummy Data – How to use the Random Functions

Using collected or known data is the best when developing Excel models, but from time to time this may not be available when you are developing your model.

This post will look at some options for setting up Dummy Data using Excels Random functions.

 

Variability

Real data displays a range of variability, but this variability is generally within ranges or distributions of ranges of results.

All fields type can contain variability

ie: Country, State Names and Zip/Postal Codes, Maybe large lists but are fixed

Peoples Names, Maybe a large lists but are fixed by local rules

Ages, generally less than 80, never less than 0

Dates: Rarely before 1990 or 1900 in rare cases

Lists: are fixed

Numbers: generally random or conforming to a fixed distribution or known trend

Numbers: may include integers, decimals, negatives, extremely large numbers or all combinations

In generating random lists you will need to choose if you want random data, random data within constraints or random with a distribution. The choice is really yours and should in part be based on what the data is being used for and how accurately it needs to reflect reality.

 

Techniques

The techniques described below are all shown with a worked example in the attached Examples File or the Excel 2003 Example

 

Each example is annotated below like (Example 4.). ie: Refer to Example 4 in the above example files.

Dates

Setting up Random Dates is a simple process using the Date function.

=Randbetween(StartDate,EndDate)

 

Dates in a Range of Years

=Randbetween(Date(2000,1,1),Date(2011,12,31))

Will give a list of Random dates between 1 Jan 2000 and 31 Dec 2011 (Example 1.)

(Thanx Mike W)

Dates in a Month

=Date(2010, 6, Randbetween(1,30)

Will give a list of Random dates between 1 June 2010 and 30 June 2010 (Example 2.)

 

Don’t worry that the above formula (Example 1) can actually produce a 31 Feb 2005, the Date function will happily convert that to 3 March 2005 (Example 3.)

 

Dates within a Date Distribution

=DATE(2011,7,NORMINV(RAND(), 0,60))

Will give a list of Random dates between approximately 1 Jan 2010 and 31 Dec 2010, with a mean of July 1 and standard deviation of 2 Months (60days) (Example 4.)

Where NORMINV(RAND(), 0,60) will return values between -180 and +180, 99.7% of the time

 

Text Fields

Dependant on how many items in the list you require there are 3 techniques available

Choose

For small lists of less than 6 to 10 items you can use a simple Choose function (Example 5.)

=Choose(Randbetween(1,6),”Item 1″, “Item 2”, “Item 3”, “Item 4”, “Item 5”, “Item 6”)

VLookup

Using VLookup (Example 6.)

=Vlookup(Randbetween(1,List Length), List, 2)

Index

Using Index (Example 7.)

=Index(List, Randbetween(1, Counta(List) ))

Numbers

Small Random List of Numbers

Random from a small list of numbers (Example 8.)

=Choose(Randbetween(1,6), Numb 1, Numb 2, Numb 3, Numb 4, Numb 5, Numb 6 )

Note that the numbers:

• Don’t have to be in any order,
• Can be integers, negatives or contain decimals
• Can be repeated

eg: =Choose(Randbetween(1,6), 18, 21, -19, 36.4, 18, 24)

 

Random Integers

Return Integers between Start and Finish (Example 9.)

=Randbetween(Start, Finish)

=Randbetween(50, 100)

Will return an Integer between 50 and 100

Random Numbers

=Rand()

Will return a random number between 0 and 1

=Round(Rand()*100, 2)

Will Return Numbers between 0 and 100 with 2 Decimal places (Example 10.)

Random Numbers Based on a Distribution

=Norminv(Rand(), Mean, SD)

Will return a random number between 0 and 1 based on a distribution of Average = Mean and Standard Deviation = SD

=Norminv(Rand(), 50, 17)

Will return a random number between 0 and 100 based on a distribution of Average = 50 and Standard Deviation = 17, (Example 11.)

Random Numbers Fitting a Trend

If your distribution has to match a trend add a Random component to the Trends equation (Example 12.)

Y=mX+c

= rand() * X + rand()*5

= rand() * A2 + rand()*5

 

True/False

Choose

Use Choose and Randbetween (Example 13.)

=Choose(Randbetween(1,2), True, False)

If

Use If and Rand (Example 14.)

=If(Rand()<0.5, True, False)

 

Combination Text and Numbers

The above techniques can be combined to make lists of Alpha Numeric Data

Say your business has a fleet of vehicles (TR=Truck, VN=Van, CAR=Car)

=Choose(Randbetween(1,3),”TR”,”VN”,”CAR”) & Text(Randbetween(1,15),”0#”)

Will randomly choose 1 of “TR”,”VN”,”CAR” and add a random number between 1 and 15 to it format with a leading 0, eg: TR05, (Example 15.)

 

Other Sources of Data

Random Data

There are a number of web sites where Random Data is available.

http://www.fakenamegenerator.com/order.php

http://www.generatedata.com/#generator

http://www.melissadata.com/lookups/

Open Source Data

There are a number of web sites where Open Source Data is available.

http://en.wikipedia.org/

http://www.google.com/

http://www.readwriteweb.com/archives/where_to_find_open_data_on_the.php

 

Function Used:

Rand: Returns a random number between 0 and 1.

Randbetween: Returns a random Integer between lower and upper limits. Pre Excel 2007 Randbetween was only available through installation of the Analysis Toolpak (Thanx Luke).

Norminv: Returns the inverse of the normal cumulative distribution. That is it returns the X value from a Normal Distribution that has a know Mean and Standard Deviation where the a known cumulative percentage is supplied.

Choose: Choose an item from a list of up to 254 items.

Vlookup: Lookup the matching value from a list and return a data item from another column from the same location.

Index: Retrieve an items from a defined location within a range.

Text: Displays a number as Text with a defined format.

 

Other Uses of Random Functions

Of course the techniques shown here don’t have to be used for setting up Dummy Data.

One area where Random numbers is used is in Monte Carlo Simulation. This has been discussed at Chandoo.org at Data Tables and Monte-Carlo Simulations in Excel a Comprehensive Guide

 

Techniques

The techniques described above are all shown with a worked example in the attached Examples File or the Examples File 2003 ver

 

Limitations in Pre Excel 2007 versions

The Excel function, Randbetween, was only introduced in Excel 2007. As such the exaples above will only work in 2007/10.

However a simple alternative is available

Randbetween(Low, High) = Low + Int(Rand()*(High-Low))+1

Randbetween(90, 100) = 90 + Int(Rand()*10)+1

Examples using this approach are shown in the 2003 Version of the Examples files above.

 

How have you made Dummy Data or used the Random Functions?

How have you made Dummy Data or How have you used it ?

How have you used Random Numbers in your workbooks ?

Let us know in the comments below: