For the past week or two, I have been working on building an online course through Udemy and it has been consuming most of my free time so I haven’t had a whole lot of opportunity to post anything here on my blog. That being said, I’ve been looking around for ways to generate random logical sample business data to help build up data sets and samples for my course. As a result, one thing that I’ve started to do is use the Index Randbetween CountA formula to pull data from one table located on a separate tab/worksheet that may hold a smaller set of data that will be required to build a full sample data set.
This example in particular shows you how to pull types of transactions from a transaction type table into a transaction history table and generate a random sample of records by using this Index Randbetween Counta formula combination. Have a look below and if you have any questions or other suggestions feel free to post them in the comments section.
INDEX RANDBETWEEN AND COUNTA FORMULA EXPLAINED
- INDEX(array,row_num,[column_num])
When using the index formula, it is not necessary that you reference both a row number and a column number. The index formula says to lookup a certain range where the row_num and/or column_num will be integers and to return whatever value you choose. For example if you choose row 2 within the array (row_num=2), the formula would return the value of the second row cell of your range selection. If you chose to do add a row_num and a column_num, it would be the intersection of the row number and the column number.
- RANDBETWEEN(BOTTOM,TOP)
The randbetween formula will provide you with a value between bottom and top. If your bottom value is 1 and your top value is 5, each time you refresh your screen, a new value between 1 and 5 will be displayed in that cell.
- COUNTA(VALUE1,[VALUE2]….)
The COUNTA formula allows you to select a range or multiple cells that are numeric, text or formulas and count how many of the cells actually have values within them. If you were to use the count formula, the count formula would not count the cells with text in its tally.
Great now how do we combine these three formulas to build random sample data. Let’s use the simple examples above to explain how, then I’ll show some screen shots of the actual example.
Our new hybrid formula: =INDEX(N6:N9,RANDBETWEEN(1,COUNTA(N6:N9)))
We are indexing and looking at an array of cells N6:N9 and we want to return a random value between 1 and the total number of counted cells in the range N6:N9 for our row (our row is the value needed in order to complete our index formula). The index formula will then select a random cell within N6:N9 using the row number 1-4. Depending on which number is randomly picked by the formula, the corresponding cell text/value will be displayed for the row within the cell range N6:N9 (JOHN, DAVID, MARK, LARRY).
In our example below, we will show you how to use the formula with table names and fields which will make our random data generation dynamic based on the values on the lookup table.
That pretty much sums up how to use the Index, Randbetween and CountA formula to build random sample data. See you next week!
Download the Free Excel Workbook!
