Whenever you have a formula or macro for a task, you would like it to be tested for some sample data. This sample could be real one – i.e. obtained from the historical record or you may test it with a some fake or random data. When it comes to testing formulas and macros, random data testing is a very common practice because it is not unusual that historical records may be absent in some cases.
In this post, we will try to learn how to:
- Make random data with RANDBETWEEN() function
- How to obtain it for free from internet
This entire post of ours will be dealing with “Random Data”. You can always download Sample Workbook to get started from.
The RANDBETWEEN() and RAND() formula:
Both of the above formulas generate random numbers – the first one generates it between any two number greater then on and less then defined number. The actual Syntax for the formula is:
=RANDBETWEEN(bottom, top)
When we tell the formula the top and the bottom values, the formula start giving us random numbers in the range.
The second formula RAND() does not take any argument and gives random numbers between 0 and 1 i.e. the values are in decimal numbers.
Random numbers could be required for different type of quantities. It could represent Sales, Product Types, Customer Identity, Seller Identity, Employee codes and so one. Lets take the following table as an example and see how we can generate data to suit the fields.
Generating Years
Field Type: Numeric
Years are always numbers. Lets assume that we want to have between 2010 and 2016. We can specify the range as hard coded numbers or we can refer to cell with this data. Refereeing to cell is a more professional way of doing this so we will enter in B3 the following formula, referring to the required data on Sheet2.
=RANDBETWEEN('Sht2'!C2,'Sht2'!C3)
As we drag down the formula, we will have the years place in the column B. when done, copy the column and paste special as numbers to preserve the numbers.
Generating Quarters
Field Type: Alpha-Numeric or Numeric
Quarters are usually represented by the key-letter “Q” followed by quarter number. A year has four quarters so it could be between Q1 to Q4 that need to filled in the “Quarter” in column C. We will generated the number numbers between 1 and 4 and the number will be preceded by letter “Q”.
="Q"&RANDBETWEEN(1,4)
Since a year can not have three quarter – the formula arguments can be hard coded. The final result looks like:
Generating Product IDs and Prod Category
Field Type: Alpha-Numeric or Numeric
The product ID represents SKUs in Inventory Management Terminology. SKU means a stock keeping unit. This field could have a parent product as a first part and the subcategory type as second one to present the exact SKU or if you want more detail you can add more subtypes as well. For Example:
A t-shirt with four different colors and three different sizes makes 12 different SKUs.
To keep it simple and short, we want to generate something like XXX-0000 like code. From the following list of SKU and using random numbers we can have following formula to give us result
=VLOOKUP(RANDBETWEEN(1,8),'Sht2'!$E$2:$G$9,3,0)&"_"&RANDBETWEEN(1,20)
Product Category could be fast moving or slow moving goods depending and can be obtained on the basis of Production ID, the formula we will use is:
=VLOOKUP(LEFT(D3,3),'Sht2'!$G$2:$H$9,2,0)
The LEFT() function here looks at the left side of the Prod ID and fetches the three characters that identify the product and uses VLOOKUP() to get the respective Product Category.
Sales Person ID and Quantity
Field Type: Numeric
Simply use formula =RANDBETWEEN(1,50) for ID, replace top and bottom values by number of your choice to get IDs as well as quantity sold. If you want to get values as multiples of 10 for Quantity, simply multiply formula by something like 10, 100, 1000 etc.
The final table will look like:
Online resources for random data:
There are various online resources available that gives us limited amount of data for free accounts. Few of such as:
Mockaroo – Random Data Generator | CSV / JSON / SQL / Excel
https://www.mockaroo.com/
GenerateData.com
http://www.generatedata.com/
RANDOM.ORG – Integer Generator
https://www.random.org/integers/
…and many more you can find through Google.
Please download the sample file to see how these formula works.