How to Discharge Excess Data Randomly using Spreadsheet in 5 Steps

This is a simple how to use spreadsheet (you could use Libre Office or Excell for example) to make your sample in your research become a balance sample. SPSS have specific menu to do this, under menu Analyze – Complex samples – Select a Sample.

If you do not have one, and usually in every computer, spreadsheet is widely available(even the open source software). We can use spreadsheet as the tools do the job.

This is the scenario : you have three groups of samples A , B and C. Group A consist of 13 cases, Group B consist of 12 cases, and Group C is 10. To make it balance, you need to remove some of the cases in Group A and B to become 10. Therefor, you have to make sure that when you do it, it has to be random.Is it ok ? Yes, some references mention that a random from spreadsheet software is consider as random enough. Do you want to have more random number ? there are wide variety of website available to produce a random number.

This sample below is Group A, you should repeat the procedure for other group. As you can see, you have id and total score column.

STEP 1
Create step 1 column, and create formula ” =RAND() “. This formula is a fuction to generate random number.
Drag that formula all the way down, so you will have random number in the Step 1 column
The result of random number. It create random number between0 and 9.999999….

STEP 2
Create Step 2 column. Copy Step 1 column, and paste special (value only) to Step 2 column
Do you now why we should do this ? Because by the design, a RAND function always create random number everytime we give order, fill, del or anything in the spreadsheet, therefor we need a fix result from random number that wont be change.

STEP 3
Create number from 1 to 10 in left side of the id column. This is the amount of the sample that we need to make balanced sample in our case.

STEP 4
Block the column Id to Step 2. Short the column (ascending) with the Step 2 column as the reference.
The result is an ascending order of the cases (samples) based on Step 2 column, as seen as below.

STEP 5
The last step is, delete the case below number 10. Congratulation !! You have randomly discharge 3 samples out of 10.

I use this method using Libre office and Ms Excell.
If you have other method using spreadsheet, please feel free to share in the comment.

Leave a Reply

Your email address will not be published. Required fields are marked *