Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel random sample with criterion
I need to create a macro or function that will pull a random sample from
column B, but only when the criterion in column A is met. For example: Column A Column B A 123 A 456 A 789 B 234 I need to pull a random sample of the values in column b, but need the ability to select the column A value. I was hoping for a button or formula that i could move from sheet to sheet since we get a system generated list each week. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel random sample with criterion
Here is a neat trick:
The usual technique is to put =RAND() down column C and then sort by column C. This will shuffle cols A & B. Instead put: =(A1="A")*100 + RAND() in C1 and copy down. Now each sort (descending) will put all the "A"'s at the top in a random order. Just keep picking B1 after each sort. -- Gary's Student "gilgamesh2006" wrote: I need to create a macro or function that will pull a random sample from column B, but only when the criterion in column A is met. For example: Column A Column B A 123 A 456 A 789 B 234 I need to pull a random sample of the values in column b, but need the ability to select the column A value. I was hoping for a button or formula that i could move from sheet to sheet since we get a system generated list each week. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
excel random sample with criterion
Hi, if you only need to select one of the numbers in B:B, the following
*array* formula might also work for you (assuming data in A2:B8): =SMALL(IF(A2:A8="A",B2:B8),INT(RAND()*COUNTIF(A2:A 8,"A"))+1) As an array formula it must be entered with the key combination Shift+Ctrl+Enter. HTH Kostis Vezerides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Find Numeric Criterion in Column & Return the Numeric Value from Row above | Excel Worksheet Functions | |||
Random Sample Without Duplication | Excel Discussion (Misc queries) | |||
How do I generate random lottery numbers in Excel? | Excel Worksheet Functions | |||
Generating Correlated Random Values in Excel | Excel Discussion (Misc queries) |