What excel function to use to generate some words with a certain frequency?

[pocketrocket]

What excel function(s) can I use if I want to generate some random words with a certain frequency?

For example, I want:
Apple to appear 10% of the time
Banana to appear 20% of the time
Cherry to appear 20% of the time
Dates to appear 50% of the time

Thank you!

[Ron Coderre]

With
C1: apple
D1: banana
E1: cherry
F1: dates

Try this:

Please Login or Register  to view this content.

Copy that formula down as far as needed.

If you use that formula in A1:A2000, these formulas verify the results.

The count of each item:

Please Login or Register  to view this content.

The percentage of C1 (apple):

Please Login or Register  to view this content.

Format E1 as %

Copy those formulas down through Row_4

Is that something you can work with?

[shg]

Or =INDEX($C$1:$C$4, MATCH(RAND(), {0,0.1,0.3,0.5}, 1))

[Ron Coderre]

Or =INDEX($C$1:$C$4, MATCH(RAND(), {0,0.1,0.3,0.5}, 1))

Wouldn't each of the direct hits for 10%, 30%, and 50% be associated with the next item in the list?

examples:
10% would be associated with the 2nd list item, instead of the 1st
30% would be associated with the 3rd list item, instead of the 2nd
50% would be associated with the 4th list item, instead of the 3rd

...or am I interpreting this wrong?

[shg]

Match returns the largest value less than or equal to lookup value, so

RAND() = 0.0 to < 0.1 returns 1
RAND() = 0.1 to < 0.3 returns 2
RAND() = 0.3 to < 0.5 returns 3
RAND() = 0.5 and up returns 4

[Ron Coderre]

Right....so when the RAND function generates 0.1...the 1st list item should be
selected (since we want 10% of the items to match the 1st list item).

In this formula, the second item would be returned:

Please Login or Register  to view this content.

Consequently, over a large selection the first item's percentage
would approach....but never reach...10%.

In this formula:

Please Login or Register  to view this content.

The FREQUENCY function performs an upper limit match, so
• 0-10 would select the 1st item,
• >10 through 30 would select the 2nd item
• >30 through 50 would select the 3rd item
etc

...or am I missing something "obvious"? :\

[shg]

Consequently, over a large selection the first item's percentage would approach....but never reach...10%.

Uh-uh.

Over a large selection, and assuming RAND() returns a reasonably random uniform distribution, the mean percentage of apples will be 10%.