Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull a Sample from a Population
I need a statistically valid, randomly selected breakout of transactions from
a total population of all transactions. Would like to have Excel generate a statistically valid list from a total population of about 38K transactions. All 38K transactions, regardless of dollar value, are subject to review through random selection based on the n of total population. The list should also incude any transaction = $5,000. The listing should include all data elements of the select transactions. Possible? How? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull a Sample from a Population
Dazed,
Insert a column of formulas, like this (assumes that the amount is in column D, for example) =OR(D2=5000,(Rand()*100)99) Change the 99 to a lower number to choose a greater # of items randomly... Then filter your database based on the column of formulas, and choose to show TRUE. The transactions still visible are those that should be reviewed. HTH, Bernie MS Excel MVP "dzd&confused" wrote in message ... I need a statistically valid, randomly selected breakout of transactions from a total population of all transactions. Would like to have Excel generate a statistically valid list from a total population of about 38K transactions. All 38K transactions, regardless of dollar value, are subject to review through random selection based on the n of total population. The list should also incude any transaction = $5,000. The listing should include all data elements of the select transactions. Possible? How? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull a Sample from a Population
Got it! Thanks! Jon
"Bernie Deitrick" wrote: Dazed, Insert a column of formulas, like this (assumes that the amount is in column D, for example) =OR(D2=5000,(Rand()*100)99) Change the 99 to a lower number to choose a greater # of items randomly... Then filter your database based on the column of formulas, and choose to show TRUE. The transactions still visible are those that should be reviewed. HTH, Bernie MS Excel MVP "dzd&confused" wrote in message ... I need a statistically valid, randomly selected breakout of transactions from a total population of all transactions. Would like to have Excel generate a statistically valid list from a total population of about 38K transactions. All 38K transactions, regardless of dollar value, are subject to review through random selection based on the n of total population. The list should also incude any transaction = $5,000. The listing should include all data elements of the select transactions. Possible? How? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull a Sample from a Population
I would create a new column with the formula in row 2: =IF(D25000,0,RAND())
(assuming that D is the column with the transaction amounts), then copy it down till the row 38,000. Then I would select the entire column, press Ctrl-C, then right-click, select Paste Special and click on Value. This way, this new column has a set of random numbers (or 0's) and it is frozen. Then, I would use AutoFilter to filter on this column, use Custom and select all the values less than 0.05 if I want 5% of the transactions, or less than 0.23 if I want 23% of the transactions. As transactions above $5,000 have a value 0, they are always selected. Then, you can copy the filtered range somewhere else and perform the review of the transactions. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200708/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic Population | Excel Discussion (Misc queries) | |||
Date Population | Excel Worksheet Functions | |||
Population % change | Setting up and Configuration of Excel | |||
Every Sat Date Population | Excel Discussion (Misc queries) | |||
How do I pull a random sample of people from a list in excel? | Excel Worksheet Functions |