Random String Generate based on Probabilities

[skydivetom]

Experts:

I need some assistance with creating an Excel formula which will generate string values based on both "lookup" values and their associated probabilities. See details below (as well as attached XLS).

Background:
- I have 3 look-up areas covering demographics "Race" (A2:A8); "Gender" (D2:D4), and "State" (G2:G6).
- The lists are merely for *demo* purposes. Naturally, the list (e.g., "State") may grow to include all 50 states (vs. just listing 5 US States).
- Next to each value, I have some completely *arbitrary* probabilities. The sum of each probabilities (column B, E, and H) adds up to be 100%. Again, these % are purely for demo purposes.

What I Want to Achieve (via e.g., Excel formula):
- I have a matrix with 30 blank records (K2:M31).
- Now, I want to generate some random data for each of the 3 columns (K:M); however, I want the number of random values to be generated based on their individual probabilities.

Example:
- In cell range M2:M31 (State), I want to generate values where "CA" will approximately occur 12 times (30 records * 40%). Now, if it turns out that "CA" would exist 11 or 13 times, that will be perfectly fine.
- Overall though, once the matrix has been populated with 30 randomly generated values, the overall distribution of the values should somewhat be representative of the indivudal probabilties.

My Question:
- Is there a way I can use some RANDOM formula that populates the matrix accordingly?
- Please keep in mind that my lookup values (columns A, D, G) could increase or decrease.
- Also, for demo purposes, my matrix is only 30 rows deep. However, I need to be able to adjust any formulas so that I could generate a matrix with, e.g., 1000 records. Is that possible? If yes, how?

Thank you in advance,
EEH

[Bo_Ry]

Please try at
O2:Q50
=IF($J2,RAND(),"")

K2:M50

Please Login or Register  to view this content.

[skydivetom]

Bo_Ry:

Wow... that's totally awesome... I truly appreciate your help on this.

Quick follow-up questions:
I have additional metrics (e.g., military ranks, etc.) that I also need to integrate into the list. What's the easiest way to add additional metrics into this schema? The formulas are rather long. Is there a way to simplify it by running the function one column at a time?

Also, where do I change the value(s) if I want to increase the matrix from 30 records to, e.g., 100 records?

[hrlngrv]

The simplest way to do this is to use tables with the % values representing CUMULATIVE probabilities. For example, for gender (smallest), use

0%	Female
23%	Male
98%	Unknown

If this were named GenderTbl, you could formulas like

Please Login or Register  to view this content.

to generate random Gender values following the distribution in the 1st column of GenderTbl. There are other ways to arrange this, but they'd require using 3 arguments with LOOKUP. Tables like the one above allow for the simplest LOOKUP formulas.

I've supplemented your workbook below.

[skydivetom]

hrlngrv - thanks for chiming in... I'll check it out. Thank you!

[skydivetom]

hrlngrv -- wow... I really like how simple this solution is!! It's very easy to customize and add additional metrics.

I'm loving it!!!