Within Genstat calculations are performed on whole columns rather than cell by cell. As with Excel, you need to specify a formula or expression to perform the calculation. The Calculate dialog lets you create numerical or logical expressions to evaluate data in your spreadsheet and store the calculated results in a new column.
The empty field at the top of the calculator lets you construct your expression by combining data structures and operators. You can use brackets to control the order of evaluation when required.
- From the menu select Spread | Calculate | Column.
- Select which columns you want to load into the Available data list by selecting the checkboxes for Variates, Factors, etc.
- To construct your expression, double-click column names to copy them in. Type the operators directly into the expression or select them from the keypad.
- Type a name for the resulting column in the Save result in field.
- Click Run to calculate the column and add it to your spreadsheet.
|Available data||Specifies which data type columns are made available for you to build your expression.|
|Save result in||Specifies a data structure to contain the result of the expression. If this structure already exists you should ensure it is the appropriate type to store the result. For example, if you specify a matrix expression M1*+M2 to be stored in M3, M3 must either not exist or be a matrix of the appropriate dimensions.|
|Keypad||Use the keypad to enter operators into the expression being formed. Click the keypad to view an explanation of each of the functions. You can also type the corresponding text directly into the expression, but note that the logical operators (and, eqs, or, nes, not, is, in, ni, eor, isnt) must be delimited by dots (for example .and.).|
|Functions||Opens the Calculate Functions dialog allowing you to insert a function into your expression.|
|Display in output||Lets you display the resultant values in the Output window.|
|Display in spreadsheet||Lets you display the results in a spreadsheet. You can select the sheet from the list of current open spreadsheets or request a new spreadsheet is created. Note: the number of rows of the spreadsheet must match the length of the results formed by the calculation, otherwise a new sheet will be used.|
The images below show the result of calculating the difference between the two columns Initial Lwt and Final Lwt. The resulting column is called Lwt Change and is coloured half yellow to indicate that this is a calculated column.
If you edit any of the values in the columns used to form the expression then update the server (for example, by clicking outside the spreadsheet), you will be prompted to recalculate the calculated column. You can turn off this prompt if desired by selecting Tools | Spreadsheet Options and clicking the General tab. Deselect the option Prompt to update calculated columns to disable this feature.
You can re-display and also edit the expression by double-clicking the calculated column’s header. This opens the Column Attributes/Format dialog.
After editing an expression and clicking OK the following prompt will appear, allowing you to recalculate the column using the revised expression.