April 9

Generating random qualitative data in a pinch

11  comments

Generating random qualitative data in a pinch

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.

image

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.

Loved this? Spread the word


About the Author

Jordan Goldmeier

Related posts

Oz’s Excel Tip: Keep a Workbook for Random Data in Excel

Read More

How to create a relative file path in Power Query

Read More

Make Your Excel Spreadsheet into a Bitmap Image With This Excel Magic Trick

Read More

Using Power Query to Connect Tables for Reporting | Excel.TV, Episode 55

Read More
  • 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.

  • Hello Jordan,
    Here is a slightly shorter formula: =CHOOSE(1+4*RAND(), “one”, “low”,”medium”,”high”)
    Best regards. Hervé Thiriez.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Subscribe to our newsletter now!

    >