There are plenty of terrific random data generators for Excel out there. But sometimes you just need some fake data qualitative data without the fuss.
For instance, perhaps I want to generate a random list of data points with the values low, medium, or high. A simple method I recommend combines RANDBETWEEN with CHOOSE. Take a look:
=CHOOSE(RANDBETWEEN(1,3), “low”, “medium”, “high”)
All I would need to do now is use the formula above and then drag down to create a list of random, qualitative data.
If I want more weight given to a certain variable, say I want low to appear 50% of the time, I can adjust the bounds of my RANDBETWEEN and repeat the data item.
=CHOOSE(RANDBETWEEN(1,4), “low”, “low”, “medium”, “high”)
It’s not the most elegant solution, I admit. But like I said, it does well in a pinch.
- All Excel LOOKUPs Explained - May 26, 2020
- How to: Power Query File From Folder - April 21, 2020
- Oz’s Excel Tip: Keep a Workbook for Random Data in Excel - January 23, 2020
That’s a great technique! I used to use a vlookup to bring in random qualitative data, but this a much faster solution. Thanks for sharing!
Reblogged this on CareWare.
Here’s how I used to do it: copy/paste, paste, paste, paste, copy/paste, paste, paste, paste, etc…yeah, pretty inefficient!
hi,kindly guide me . i am making mistake by applying this formula
If you are copying and pasting the formula, Excel is probably throwing an error because of the quotation ” symbol used. Retype the quotes around everything when pasted into Excel.
Those darn smart quotes! Always causing trouble
They’ll getcha every time!
I have a similar approach, but I assume rand produces a uniformly distributed distribution, which when multipled by itself acts like the the variable x in x^2. You can then use multiple rands and power commands to produce equations, which become stepwise when one of the round functions are applied. This allows for a rich variation in randomness when you encode the rounded values to qualitative data. I use this to produce simulated web traffic data, which can be used as a practice set for analysts.
Example pseudo code: Choose ( roundup ( power (randbetween (1, 4), 2), 0), “low”,”middle”,”high”)
E-mail me at [email protected] for completely auto-generated web traffic simulation data. It’s not realistic model, but demonstrates a few different randomization techniques.
Hello Jordan,
Here is a slightly shorter formula: =CHOOSE(1+4*RAND(), “one”, “low”,”medium”,”high”)
Best regards. Hervé Thiriez.