10% & 25% Random Sample

[covershaker]

Hello, How do I use rand() to select a 10% and 25% sample of a excel list. I want to select a random 10% when Rsk Lvl is low and 25% when Rsk Lvl id Med. Can it be done using rand()?

Sample Data:

Sub# Rsk Lvl Random#
22 Low .14402889
23 Med .44429761
24 Med .55569938
25 Med .09196017
26 Med .48338882
27 Low .82736503
28 Low .30690989


Thank You.

[Beamernsw]

Maybe something along the lines of this will help you.

Please Login or Register  to view this content.

[jomaor1]

Hi,

Do you mean that if Low, then it should be between 0% and 10%, and if Med it should be between 10% and 100%? If yes then:

Please Login or Register  to view this content.

in cell D2

[covershaker]

Thanks both are good suggestions. One of my teammates suggested the following: if(and(b2='Med',rand() <.25), "Select",if(and(b2='Low', rand() < .1), "Select","Not Select"). The one thing that I don't fully understand is, does the number generated by rand() represent a percentile of the population selected?

[Pepe Le Mokko]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[covershaker]

See workbook with sample

[joeu2004]

I want to select a random 10% when Rsk Lvl is low and 25% when Rsk Lvl id Med

Please clarify your requirements. You want to select "a random 10%" of what?! when risk level is "low", and "a random 25% of what?! when risk level is "med"?

In your attachment, column A contains 9 "low" and 9 "med". Is that the population you want to select from? Or is that an example of the result of the sampling (selection)?

One interpretation: you want to select a random 10% of "low" and a random 25% of "med". But that would be 0.9 "low" and 2.25 "med". Obviously incorrect!

Another interpretation: you want to generate 18 data, generating "low" with 10% probability and "med" with 25% probability. But that begs the question: what to generate for the remaining 65% probability? So probably incorrect, again!

Bottom line: explain the selection process carefully, step-by-step, as you would do it manually. Provide a different attachment (probably) that shows the data you are selecting from and an example sampling based on a manual selection (not formulas, since that is what you are asking for), ideally with some indication of how you made those selections (10% of this and 25% of that?).

-----

One of my teammates suggested the following: if(and(b2='Med',rand() <.25), "Select",if(and(b2='Low', rand() < .1), "Select","Not Select"). The one thing that I don't fully understand is, does the number generated by rand() represent a percentile of the population selected?

No. RAND() generates random values between 0 and less than 1, uniformly distributed. You can think of that as a percentage or probability; but it is independent of your data (population).

Suppose you want a sample of "low", "med" and "high" with probabilities 10%, 25%, 65%. (The sum of probabilities should always be 100%.) You might write:

=LOOKUP(RAND(), {0, 0.1, 0.35}, {"low", "med", "high"})

That says (in math terms, not Excel): if 0<=RAND<0.1 (10%), select "low"; if 0.1<=RAND<0.35 (35% -10% = 25%), select "med"; otherwise (100% - 35% = 65%), select "high".

What you have in your first attachment is not the same, for the purpose that I am demonstrating (which might be different from your intent!).

Instead, you associate RAND values with "low" and "med". If the RAND value associated with "low" is less than 10%, you select "low". If not, you will not select an alternative. You could end up with nothing selected because the RAND associated with "low" is never less than 10% and the RAND associated with "med" is never less than 25%, simply by chance.

Again, it is unclear if and how that relates to your requirements, because your requirements are unclear.

[covershaker]

Sorry if I was unclear. I want to select 10% of rows where Rsk Lvl is Low. 25% of rows where Rsk Lvl is Med.

[MrShorty]

It sounds like a shuffle kind of algorithm, which, in a spreadsheet usually involves adding a column of random numbers (you've already done that), then "sorting" the data based on those random numbers and the risk lvl. Finally, you take the first xx% (which means you need to count how many of each type of risk level, then take the appropriate percentage of that count, then round up or down to the nearest integer as you deem appropriate). In your sample file, I simply selected the table, Clicked on "sort" in the Data ribbon, Sorted first by risk level then by randomizer. I notice you have picked 4 of the 9 lows (45% of the lows, not really 10%) and 2 of the 9 Meds (closer to 25%).

Will that work for you, or are you looking for something more than that?