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.

&nbsp

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.