How to Randomly Select a 10% Data Sample for an Audit in Excel
Question details
The user needs to randomly select a 10% sample from five different department sheets for a Level 1 audit, and then randomly select another 10% from those extracted results for a Level 2 audit.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Performing randomized Level 1 and Level 2 audits on large sets of department records.
- Observed behavior
- The goal is to properly extract a random 10% subset from multiple sheets, followed by a secondary 10% selection on the extracted subset without bias.
Before attempting to pull random samples across multiple tabs, it is highly recommended to consolidate all five department sheets into a single master table to make the randomization formulas work seamlessly.
Use Dynamic Array Formulas (SORTBY and RANDARRAY)
Use a combination of Excel's dynamic array functions to automatically shuffle your data and extract exactly the percentage of rows you need.
This method relies on Excel's modern array functions. By generating an array of random numbers alongside your data, sorting by those random numbers, and using the INDEX function, you can effortlessly extract a random sample.
Copy and paste the data from your five department sheets into one single master sheet. Ensure there are no blank rows and that your headers are clearly defined.
Determine how many rows equal 10% of your total data. For example, if you have 1,000 rows, your sample size is 100. Place this target number in a blank cell, such as B2.
In a new worksheet, enter the formula =INDEX(SORTBY(A1:A1000,RANDARRAY(ROWS(A1:A1000))),SEQUENCE(B2)) — adjusting 'A1:A1000' to match your actual data range. This will instantly spill a randomized 10% sample of your data.
Calculate 10% of the newly extracted Level 1 results. Repeat the same formula process, this time pointing the data range to the Level 1 results and referencing the new target sample size.
Automate the Audit Selection with VBA
If you perform this multi-level audit frequently, a VBA macro can automate the consolidation and random sampling steps in one click.
Easily Perform Random Data Sampling in WPS Office
WPS Spreadsheet fully supports advanced array formulas like SORTBY, RANDARRAY, and SEQUENCE, making it incredibly easy to extract random audit samples without needing complex VBA scripts.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your consolidated department dataset.
- 2. Set your target sample size: Calculate what 10% of your total rows amounts to, and type that whole number into an empty cell (e.g., cell B2).
- 3. Apply the random array formula: Type =INDEX(SORTBY(A2:D100,RANDARRAY(ROWS(A2:D100))),SEQUENCE(B2)) in your designated audit sheet to generate the Level 1 sample.
- 4. Lock your sample: Select the newly generated data, copy it, and right-click to 'Paste as Values' to prevent the random numbers from recalculating.
Frequently Asked Questions
Can I perform random sampling without using dynamic array formulas?
Yes. You can add a new column next to your data and use the =RAND() formula. Double-click the fill handle to apply it to all rows, then sort the entire table by that random column. Finally, manually select and copy the top 10% of rows.
How do I round the 10% calculation if my total row count isn't perfectly divisible by 10?
You can use the ROUND() or INT() functions when calculating your target sample size. For example, =ROUND(COUNTA(A:A)*0.1, 0) will give you a whole number that can be safely used in the SEQUENCE function.
Does this method work if I keep the department sheets separate?
While it is possible using complex VBA scripts or complex 3D formulas to sample directly from separate sheets, it is strongly advised to append the data into a single master sheet first. This guarantees true randomization across the entire dataset rather than skewed proportions per department.
