Excel formula will identify the sample in sheet

[rplim2016]

Hi Mr. Excel Users,

Imagine that we have 1:1 is header 2:300 is rows (source data) 299 rows...

from 2:300, in column A i need to create a formula that will tell me the 13% sample size of that 2:300 in column B.

299 * 0.13 = 38.87 rows in column A should have marked "Sample/Audit" if we would round it down to align properly, we can have 38 rows.


The logic is from the source data, the excel formula in column A will automatically identify the "Sample/Audit" in the selection.

13%

 

[Aladin Akyurek]

Shouldn't the 13% sample be a random sample?

 

[rplim2016]

Shouldn't the 13% sample be a random sample?

Hello Aladin,

Yeah, 13% random sample would work, in column A, 38 randomly rows should have values of "Sample/Audit"

 

[Aladin Akyurek]

Let Sheet1!B1:B300 house the data with a header in B1.

In C2 enter and copy down:

=IF(B2="","",RAND())

Select the C range. Copy it and run immediately Paste Values. This step stops generating new random numbers.

In D2 enter and copy down:

=IF(B2="","",RANK(C2,$C$2:$C$300))

Sheet2 is used to gather a random sample of 13% from the data in column B of Sheet1.

In A1 of Sheet2 enter:

13%

In A2 of Sheet2 enter:

=SUMPRODUCT((1-(Sheet1!$B$2:$B$300="")))

In A3 of Sheet2 enter:

=ROUNDDOWN(A1*A2,0)

Leave A4 of Sheet2 empty.

In A5 of Sheet2 enter:

=AND(Sheet1!$D2>=1,Sheet1!$D2<=$A$3)

Note. A4:A5 constitutes the criteria range we use to create the random sample.

The recipe that follows shows how to use Advanced Filter.

Select A4:A5 of Sheet2 and fire up Advanced Filter (Data | Advanced).

Set the Action check to 'Copy to another location'.

Set List range to Sheet1!$B$1:$D$300.

Set Criteria range to Sheet2!$A$5:$A$6.

Set Copy to Sheet2!$A$6.

Click OK.