Transform - Worksheet

Click the Data | Data | Transform command to open the Transform dialog, where you can apply mathematical transformations to the columns, rows, or cells. Valid math operators include addition (+), subtraction (-), multiplication (*), and division (/) as well as a large library of built-in mathematical functions. Parentheses should be used to override precedence or for clarification.

 

Use the Transform dialog to apply math functions to data. The dialog options update to reflect the option selected for Transform with field.

 

Transform With

Select the type of transform from the Transform with list. Column variables (e.g., C = A + B) applies the transform equation to the specified rows in the Transform equation column. Row variables (i.e., _3 = _1 + _2) applies the transform equation to the specified columns in the Transform equation row. Cell variables (i.e., C3 = A1 + B2) applies the transform equation only to the cell specified in the Transform equation.

 

Transform Equation

Type the formula into the Transform equation box. Formulas consist of a destination column, row, or cell on the left side of the equation and a mathematical manipulation on the right side of the equation. Use the column label letters, row numbers, or cell locations on both sides of the equation. Click the down arrow to use previously entered equations. For columns, a sample equation may be C = A + B. For rows, a sample equation is _4=_1+_2. For cells, a sample equation would look like C2=A1+B1-C1.

 

If the transform method is by column, the range functions (sum, avg, std, rowmin and rowmax) take column indices only, i.e., sum(A...C). If transform method is by variable rows, the range functions take row indices only, i.e., sum(_1..._3). If transform method is by variable cells, the range functions are not supported.

 

The last ten functions are stored in the Transform equation field. After ten functions are included in the list, the oldest function is replaced when a new function is added. The Transform equations are stored between sessions. To use a stored function, click the in the Transform equation box and select a function from the list. Note that the First row and Last row or First col and Last col values are not saved with the stored Transform equations.

 

First and Last Columns and Rows

When calculating transformations on columns, enter the First row and the Last row to limit the calculation to the specified rows. When calculating transformations on rows, enter the First col and Last col to limit the calculation to the specified columns. When calculating transformations on cells, the First row, Last row, First col, and Last col options are not available.

 

By default, these are set to the first row and last row (or first column and last column) with text or numbers entered into a cell for the entire worksheet.

 

Empty Cells

The Empty cells option controls how empty cells are treated in the calculations of formulas. Available options are Blank the result, Are treated as the number zero (0), and Are treated as empty text (""). The default option is Blank the result, which results in the formula not being calculated for any row that contains a blank cell in any of transform equation rows or columns.

 

Text Cells

The Text cells option controls how text cells are treated in the calculations of formulas. Available options are Blank the result, Are treated as text, Are converted to numbers (if possible), and Are treated as the number zero (0). The default option is Blank the result, which results in the formula not being calculated for any row that contains a text cell in any of transform equation rows or columns.

 

Number Cells

The Number cells option controls how numeric cells are treated in the calculations of formulas. Available options are Blank the result, Are treated as numeric values, Are converted to text, and Are treated as empty text (""). The default option is Are treated as numeric values, which results in the formula being calculated for any row that contains numbers in any of transform equation rows or columns.

 

Combining Text, Numbers, and Empty Cells

Many possible combinations of the Empty cells, Text cells, and Number cells exist to allow combining these different types of cells in a Transform equation. If the transform result is not what you expect, check the settings for these options and adjust if necessary.

 

Functions

Click the Functions >> button to display a list of predefined mathematical functions. Click the Functions << button again to hide the list of predefined mathematical functions.

 

To use a function, place the cursor in the location to add a function, select a function from the list, click the Insert button, and then replace the X in the function with a column letter (A); underscore and row number (_1); or cell location (A1). Also, be sure to use proper mathematical operators (+_*/) between the function and the rest of the equation. The definition of the function is listed below the Function name list when a function is selected.

 

Insert

When the Functions are expanded, the Insert button is visible. Click the Insert button to add a function to the Transform equation box. In the Transform equation box, manually change the variable (i.e. X or Y) in the listed functions to a column letter, row number (_1), or cell location.

 

Examples

An example of a column formula is C = A + B. Columns A and B are added and inserted into column C with this equation. The formula adds the contents of A and B in each row and places the results in column C for that row.

 

An example of a row formula is _4=_1+_2. Rows 1 and 2 are added and inserted into row 4 with this equation. The formula adds the contents of the 1 and 2 in each column listed between the First col and Last col values and places the results in row 4 for that column.

 

An example of a cell formula is C2=A1+B1-C1. The value in C1 is subtracted from the sum of the values in cells A1 and B1. The result is inserted into cell C2 with this equation.

 

Example Functions

This example shows how to use the built in functions. Consider, for example, taking the cosine of data in column C. Column D is the first empty column, so we will use column D as the destination column.

  1. Click the Data | Data | Transform command to open the Transform dialog. You do not need to highlight any columns before selecting Transform.

  2. In the Transform equation box, type "D = " without the quotes.

  3. Click the Functions button.

  4. Double-click on the function name COS(X) in the Function name group. Alternatively, you could select a Function name and click the Insert button.

  5. COS(X) is automatically placed in the equation as "D = COS(X)" without the quotes.

  6. Replace the X in the function with the column letter containing the data to be transformed (column C). The equation will be "D = COS(C)" without the quotes.

  7. Change the First row and Last row if you wish.

  8. Make sure that Empty cells and Text cells are set to Blank the result to only calculate values with numbers.

  9. Click OK to create a new data column with column C's data transformed with the cosine.

 

 

See Also

Mathematical Functions

Data Tab Commands