# How do I calculate (transform) values of a variable?

Spreadsheet formulas. To perform data transformation and recoding operations on single variables using one formula (as opposed to a set of transformation formulas), you can use the data spreadsheet formulas. Double-click on the variable name in a spreadsheet variable that you want to transform to display the Variable specifications dialog box (see below) where a data transformation or recoding formula can be entered directly into the Long name (label or formula with Functions) box.

Following the Windows spreadsheet formula conventions (e.g., Microsoft Excel), start the formula with an "=" sign (otherwise STATISTICA will not recognize that the text is to be interpreted as a formula). For example, enter =(v1+v2)/2. Variables can be referenced by their names (e.g., Income, profit, TEST1) or numbers (e.g., v1, v2, v3, ...); v0 is the case number. Logical operators can be used to define conditional transformation expressions (e.g., = ScoreA + ScoreB). Note that <, <=, >, >=, and <> can be used to specify logical conditions.

Variable names can include special characters (e.g., spaces, plus or minus signs). Variable names that include special characters must be enclosed in quotation marks (e.g., = 'Score A' + 'Score B' ). If a quotation mark itself occurs in the variable name, use double quotation marks instead (e.g., = "A's Score" + "B's Score" ). Note that if double quotation marks are used in the name, then the variable name must be enclosed in single quotation marks.

Following the logic of the concept of "missing data," the formula will evaluate to missing data if any variable used in the formula is missing. However, several functions are supported that calculate specific statistics for ranges and/or lists of data "adjusting" for missing values. For example, the formula = mean (v1:v5, v7, sqrt(v9), TIME) will calculate the mean of variables v1, v2, v3, v4, v5, v7, square root of v9, and variable TIME, even if some of them may have missing values. Click the Functions button in the Variable specifications dialog box to access the Functions Browser facility that can be used to select functions and other elements of the syntax (for the formulas) and insert them into the formula editor. The Function Browser also is used to quickly access information on any of the functions and the complete on-line syntax documentation. After entering a formula and clicking the OK button in the Variable specifications dialog box, you are given the option to recalculate the variable now. If you choose not to recalculate at this time, you can do so later by:

Ribbon bar. Select the Data tab. In the Transformations group, click Recalculate to display the Recalculate Spreadsheet Formulas dialog box.

Classic menus. Click the button on the Spreadsheet toolbar or select Recalculate Spreadsheet Formulas from the Data menu to display the Recalculate Spreadsheet Formulas dialog box.

Or, press F9 on your keyboard.

Batch Transformation formulas. The same types of transformations described above under Spreadsheet formulas can be applied in "batch" via the Batch Transformation Formulas dialog box. To access this dialog box:

Ribbon bar. Select the Data tab. In the Transformations group, click Transforms.

Classic menus. on the Data menu, select Batch Transformation Formulas.

Use the options in the Batch Transformation Formulas dialog box to specify a list of transformation formulas involving any of the variables; these transformations will be evaluated in sequence, one by one. These options are particularly well suited (optimized) for transforming large data sets, because 1) All transformation formulas are evaluated case by case in a single pass through the data, and 2) any variable names or references on the left sides of equations that cannot be matched with variable names in the current data file can automatically be created as the transformation formulas are evaluated. Otherwise, the only differences in syntax between the Batch Transformation formulas and the Spreadsheet formulas is the support for multiple formulas in the Batch option, and also the fact that because the Batch formulas are not attached to any specific variable (in fact they can be freely copied from data file to data file), they cannot start with an equal sign, but must have a target variable (e.g., v1=... or Measure03=...) so that STATISTICA knows to which variable each formula should apply. There is also an option to "distribute" all Batch formulas into the respective variables in the spreadsheet and save them there with the data file, effectively replacing the Spreadsheet formulas (if there are any).

The STATISTICA Visual Basic programming language. If you need to write more complex data transformation programs than those that can be entered via spreadsheet formulas, the integrated programming language STATISTICA Visual Basic (SVB) can be used. The industry standard STATISTICA Visual Basic language offers incomparably more than just a "supplementary application programming language" that can be used to write custom extensions. SVB takes full advantage of the object model architecture of STATISTICA and enables you to access programmatically every aspect and virtually every detail of the functionality of the program. SVB adds an arsenal of more than 11,000 new functions to the standard comprehensive syntax of Microsoft Visual Basic, thus comprising one of the largest and richest development environments available.

STATISTICA Visual Basic programs can be executed from within STATISTICA, but because of the industry standard compatibility of SVB, you can also execute its programs from any other compatible environment (e.g., MS Excel, MS Word, or a stand-alone Visual Basic language).

The STATISTICA Visual Basic environment includes a flexible program editor and powerful debugging tools. To display the STATISTICA Visual Basic editor, select New from the File menu to display the dialog box. Select thetab, and click the OK button. When editing macro programs by typing in general Visual Basic commands or program commands specific to STATISTICA Visual Basic, the editor displays type-ahead help to illustrate the appropriate syntax. Help on the members and functions for each class (object) is also provided in-line (see also The STATISTICA VB Object Model). You can access examples and concise syntax summaries by pressing the F1 key. To learn more about a particular Visual Basic function, select the function in the STATISTICA Visual Basic editor and then press F1 on your keyboard. This will call the Interactive Syntax Help Engine and display the help for that keyword.

When executing a program, you can set breakpoints in the program, step through line by line, and observe and change the values of variables in the macro program as it is running. To summarize, STATISTICA Visual Basic is not only a powerful programming language, but it represents a very powerful professional programming environment for developing simple macros as well as complex custom applications. For more information, refer to STATISTICA Visual Basic.