TRANSFORMING DATA

Transformations change the shape of the distribution and are useful in many analyses where the Normal distribution is ideal. The square root, log (to base e or 10) and -1/y are the three most common transformations and these progressively pull in the right tail of the distribution and push out the left. There are two ways to transform data sets in Excel:

Enter the required formulae directly into the cells allocated for the transformed dataset, using the Autofill method to copy a formula from one cell to another.
Use the Function Wizard to apply a built-in function followed by the Autofill method

Transforming a single data value
Select the cell where you want the transformed value to go and click the Function Wizard button on the standard toolbar.

Select the 'Math and trig' function, find LN on the right of the display box and enter the input range into the box provided (see image on the right).
Both log and square root functions can be applied in this way but the formula -1/y has to be done by the manual method. Select the output cell, type '=' and enter the formula, eg =-1/A20. Click on the green tick on the menu bar or press enter. The formula bar at the top of the worksheet, shows changes as you build your formula.

Copying formulae and references
When cells are copied, Excel automatically adjusts relative references (such as C$4 and $E4) in the area where copied cells are pasted. C$4 instructs Excel to keep the row constant and use the column whereas $E4 means keep the column constant and use the row. You can also use the worksheet as part of the formula, so, for instance, POTATO!B16 references cell B16 in worksheet POTATO.
To copy a formula select the cell and use the fill handle to drag the formula into adjacent cells (see right)
Alternatively copy the cell and select the cells where you want the formula pasted. Then click on Edit > Paste special, select Paste Formula from the options and click OK

TRANSFORMING DATA

Basic statistics in Excel 23.2.99 Page: 9 of 25.