- BY Kevin Lehrbass
- POSTED IN Solutions
- WITH 0 COMMENTS
- PERMALINK
- STANDARD POST TYPE

We can create random data using RANDBETWEEN and INDEX functions. **What if you want to create random data that favors some values over others?** People have told me that they copy and paste or manually drag the values to repeat them. **See how we can automate this with just four easy steps!**

**Sample Data-set**

Let’s use population by state for the United States.

**STEP 1 ** **EXPANDING SUM FORMULA (Cumulative Sum)**

Our original data: ‘**State**‘ and ‘**Population**‘. Add this formula in cell C6 **=B6+C5 ** alongside the data.

**STEP 2 ** **CREATE RANDOM NUMBERS**

Add this formula **=RANDBETWEEN(1,’Original Data’!$D$5) **that creates random numbers between 1 and 319451069 (total sum of the population).

**STEP 3 ** **APPROXIMATE MATCH INTO STEP 1**

Add this formula **=MATCH(A6,’Original Data’!$C$5:$C$55,TRUE) **that bins our random number back into our expanding sum. We bin using TRUE (approximate match).

**STEP 4 ** **INDEX FUNCTION GETS OUR ANSWER**

Add this formula **=INDEX(‘Original Data’!$A$6:$A$55,B6) **Index function references our ‘State’ values using the match function result (Step 3) to display a state.

**Excel File & YouTube Video**

Here is my **Excel file** (or get it **here ** from my OneDrive). Here is my **YouTube video**.

**Solution Recap**

Step 1 runs alongside your original data. Drag it down further if you have more data.

Steps 2, 3, and 4 are dragged down as far as you want in sheet ‘Randomly Created Data’. If you don’t drag the formulas down very far you’ll see more variation.

Sheet ‘Pivot & Formulas to Verify’ shows how close our randomly generated data is to our original state population data.

**That’s it, we are finished!**

—————————————————————————————————————————–

But wait……

**Can We Vary The Randomness?**

**What if you want more variation in your random data. After all, isn’t it supposed to be random?**

*Fasten your seat belts ladies and gentlemen. It’s about to get nerdy!*

**Random Variation Options**

I have extended our solution to give us these three options:

- Use original expanding range (smallest variation)
- I want some variation. Currently set to 30.0% Based on value in cell F3
- I want to add variation manually (using columns H & I)

**Option 1** smallest variation if we’ve dragged formulas far enough in sheet ‘Randomly Created Data’

**Option 2** typing in a % value in cell F3 increases/decreases (random) the individual state population

**Option 3** manually type in a positive/negative number (added/subtracted to state population)

**Option 2** is selected above. Randomly add or subtract a percent of each state’s population.

The randomly generated ‘State’ data in column C (sheet ‘Randomly Created Data’) is updated. Refresh the pivot in sheet ‘Pivot & Formulas to Verify’ to see the variation.

*Isn’t that cool?*

**Excel File**

Here is my **Excel file** (or get it **here ** from my OneDrive) that includes the additional variation options.

**Equally Random Data-set?**

Simply change the numerical values to be the same value (i.e. 1).

Or, check out these solutions from **Exceljet** and **Contextures**.

**Random Stuff About Me**

**I like**: pineapple, mushrooms, olives, coffee, chocolate mint ice cream, NBA, listening to Spanish radio, data (obviously), my dogs, playing chess, drawing nerds and listening to music on Spotify.

**Random Facts**: I’m tall but not freakishly tall unless I’m in Costa Rica for work where the average height is lower and I’m 3+ standard deviations above the average height (hence the increased stares). I met NBA legend Bill Walton at the top of a pyramid north of Mexico City. I like book stores. I have a better than average memory (according to my siblings). I am a long-suffering Maple Leafs fan (if the Red Sox and Cubs can win we can too!).

**Strange:** Before the age of 40 I had never traveled to Europe. I simply had to go! After the initial trip I then had two business trips to Europe (3 trips to Europe in about 4 years!). Blindfolded I can tell the difference between Starbucks, Tim Hortons and Second Cup coffee. I’m a decent chess player but I could never solve a Rubick’s Cube (I got four sides once). I have an abnormal obsession with Microsoft Excel (or so I’m told). In a previous job I was allowed into the computer server room to test if databases were running. I wasn’t allowed to type or touch anything so I had to verbally spell out the SQL queries to another IT person who had permission to type.