Analyse-it
You are viewing part of the Analyse-it for Microsoft Excel user-guide. When you have read this topic we recommend you view contents of the user-guide or you can read more about Analyse-it, our statistical analysis software for Microsoft Excel.
Topic: Using Analyse-it > Preparing data in Excel > Defining variables > Transforming a variable

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.

Add to: del.icio.us del.icio.us | digg it! | Stumble upon

Part of the Analyse-it for Microsoft Excel user guide. For more information about Analyse-it, the statistics add-in for Microsoft Excel, or to download a free 30-day trial please visit http://www.analyse-it.com/. © Analyse-it Software, Ltd. Analyse-it® is a registered trademark. Microsoft® Excel® is a registered trademark of Microsoft.