You are viewing documentation for the old version 2.20 of Analyse-it. If you are using version 3.00 or later we recommend you go to the latest documentation.

Transforming a variable

Sometimes the observations for a variable are not immediately suitable for analysis and instead need to be transformed using a mathematical function. Transformations are often used to normalise the distribution of a variable, but can be used to change the scale, offset the observations or even recode groups.

Excel cell formulas and custom VBA functions can be used to transform or calculate the data for a variable. Transformed and calculated data is treated just like any other variable as far as measurement scale and observation precision are concerned.

Excel functions most commonly used for transformations are:

Transform Excel function
Natural log =LN(cell)
Inverse natural Log =EXP(cell)
Inverse / Reciprocal =1 / cell
Square root = SQRT(cell)
Log (base 10) =LOG(cell) or =LOG10(cell)

To transform the observations of a variable in a list dataset:

  1. Insert a new column in the dataset.
  2. Name the new variable.
  3. Type an Excel formula in the cells beneath to calculate the observations. The formula can base the new observations on any other variables or cells on the worksheet.
  4. Copy the formula down to the remaining cells of the column.