Transform > Compute > Formula


Use the Formula command to compute values for a variable based on numeric transformations of other variables. Expressions may include columns, numbers, and text, but not matrices or vectors.

You can compute values for numeric or string (alphanumeric) variables.

You can create new variables or replace the values of existing variables. For new variables, you can also specify the variable type and all associated properties.

You can compute values selectively for subsets of data based on logical conditions.

You can use over 300 built-in functions, including mathematical functions, statistical functions, financial functions, and text functions.


Dialog box items

Target Column:
Enter a target column where you wish to store the result of the mathematical expression.

Displays the variables in the datasheet.

Enter the expression used to compute the value of the target variable. The expression can use existing variable names, constants, arithmetic operators, and functions. You can type and edit the expression in the text box or you can also use the calculator pad, variable list, and function list to paste elements into the expression, observing the Formula rules.

Calculator buttons:
For definitions of the calculator buttons, see operators.

See Formula Functions for definitions.

Choose options you want.

oDelete formula after calculation: Check this box to delete the formula after the calculation. This option speeds up the calculations, if you select the automatic update formulas.

oColumn Type: Allows you to specify the variable type and all the associated properties.




The following example uses reg.vstz to walk you through using the basic features of the formula editor. These data were obtained in 1993 by a Growth Survey of 25,000 children from birth to 18 years of age recruited from Maternal and Child Health Centres (MCHC) and schools and were used to develop Hong Kong's current growth charts for weight, height, weight-for-age, weight-for-height and body mass index (BMI).


The sheet Human has a column called Weight. Suppose you want a new column that computes standardized weight values. To create this column using a formula to obtain its values:

1.Open the DataBook reg.vstz

2.Select the sheet Human

3.Choose the tab Transform, the group Compute and the command Formula

4.In the box beside Target Column, type the new name, Standard Weight.

5.Click in the formula Expression editing area.
Next, enter the formula that standardize the weight values by following these steps:

6.Click the double bracket button button_bracket in the formula editor keypad.

7.In Variables, double-click Weight.

8.Click the minus button button_minus in the formula editor keypad.

9.Click the function browser drop-down, and select Statistical Functions .

10.Double-click AVERAGE from the Statistical list.

11.In Variables, double-click Weight again.

12.Move the cursor at the end of the formula expression.

13.Click the division button button_division in the formula editor keypad. The result gives a missing denominator for the whole expression.

14.Double-click STDEV from the Statistical list.

15.In Variables, double-click Weight again.

16.Close the formula editor by clicking OK. The new column fills with values. When a weight value changes, the calculated std weight value automatically recalculates.




See Also:

Formula Functions | Formula Overview