excel for circular systematic sampling method

Question

for example.. I have a data of 21 numbers. I want 10 samples. interval 3..then I want to number the 17th row as 1,then 20th row 2,then it comes 23 but we have the data only 21 so 23-21=2,the 2nd row becomes 3..then on...again 5th row becomes 4..etc... how can I number a data like this.. please tell a formula.. a general formula also.

Answer 1

Let us keep things simple and assume you have your numbers in column A from A1 to A? wherever.

Now we need 3 control variables:

B1:  First

C1:  17

B2:  Interval

C2:  3

B3:  Count

C3:  =COUNTA(A:A)

As next we use column E as helper column to calculate the row numbers in column A. The first row is as in C1

E1:  =$C$1

The second is the interval below, but if we are over the end we have to subtract the amount of numbers:

E2:  =E1+$C$2-IF(E1+$C$2>$C$3,$C$3,0)

Drag the formula down till the end of your data in column A

Finally get the numbers from column A into column F

F1:  =INDEX(A:A,E1)

Drag the formula down till the end of your data in column A

Andreas.

Answer 2

Hi Jamalmuhammed,

Have you checked Andreas's suggestions?

Regards,

Yoga