Fill column
| Command: | Tools Fill column |
Description
Fills an entire column in the spreadsheet, or a range of rows in a column, with a value or formula. The Fill column command can also be used to clear a range of cells in a column.
You can select two options:
- Convert formulas to values: option to convert the formulas to their calculated values.
- Fill empty cells only: option to fill only empty cells with the new formula, and so saving the contents of the non-empty cells in the column.
Click to call the Variable editor dialog box.
In the following example, rows 1 to 100 of column A will be filled with the result of the RANDNORM(0,1) function. This function generates random numbers drawn from a Normal distribution with mean 0 and standard deviation 1.
When you save the data then the formulas that you have entered in this dialog box will also be saved in the MedCalc data file, so you can easily re-use them at a later stage.
The Fill column command can also be used to clear a range of cells in a column, by letting the Fill with field in the dialog box blank.
Some examples of useful formulas are given in the following table.
| Fill with: | Result: |
| SQRT(LENGTH) | fill with the square root of variable LENGTH |
| IF(var<20,"A","B") | recoding of variable var into two classes A and B |
| RAND(2) | the cells will be filled with 1 or 2 at random |
| RANDNORM(0,1) | fill with random numbers from a Normal distribution with mean 1 and standard deviation 0 |
| VAR1+VAR2 | fill with the sum of variables VAR1 and VAR2 |
| ROW | the cells will be filled with the row number |
| the cells will be cleared (empty Fill with field) |
How to convert a continuous variable into a dichotomous variable using the IF function
You can convert a continuous variable into a dichotomous variable by filling a column in the spreadsheet with an IF function.
The general format of this function is:
IF( Condition, result for TRUE condition, result for FALSE condition)
E.g. you have a variable AGE, that you want to convert into codes for two age groups, code 1 for age less than 30 years and code 2 for 30 years or more. You can create a new variable by entering the foluma
IF( AGE < 30 , 1 , 2 )
in the "Fill with" field of the Fill column dialog box shown above.
Note:
- You can enter the IF() formula directly as a variable in the dialog boxes for all statistical tests.
- The IF function can be nested in order to create 3 (or more) groups. When you want to convert the variable AGE into codes for age groups of less than 30 years, 30 to 39 years and 40 or more years, you can use the following (nested) formula:
IF( AGE < 30 , 1 , IF( AGE < 40 , 2 , 3 ) )
Notice the two closing brackets, one for the first and another for the second IF function.