Formula Calculations

Most often when adding calculations to assessment items, the type Formula is chosen in the Calculation Type field of the Calculation Editor. When this is chosen, the window appears as shown below, allowing you to enter an extensive range of calculations, from a simple average to complex aggregations of data across the subject.

Virtually the same Calculation Editor also appears when setting up an analysis in the Analyser window in Accelerus, when formulae need to be defined in the Filter, Formula and Aggregate operators in that window.

AI CalcWindowNumbers

Feature

When adding assessment items, from any location where calculations may be be added to them, and the Formula calculation type is selected, the Calculation Editor is displayed.

If a formula has already been added, it will automatically be displayed in this window.

There is no Calculation Type field when the Calculation Editor is invoked from the Analysis window, however.

The calculation is composed in the top pane. It may be typed directly into the pane, or available fields, functions and operators from the panes below may be inserted into it. The formula must have the correct syntax for the functions used. White space, including line feeds, in the formula may be used to make it easier to read and these are ignored when the formula is run.

The relative size of each of the panes may be changed by clicking and dragging the resizers between them.

The Fields pane contains a tree of all of the available fields for insertion in calculations. These are grouped in folders by entity type and, within each grouping, there may be further subgroupings of fields, until the fields are displayed.

The available fields will vary, depending on:

•	The window in which it has been activated, ie a subject, class or an analysis window.

Subject and class windows deal with results in assessment items, that apply to particular subjects and classes and, therefore, the fields available are specific to the particular subject and classes. On the other hand, analyses deal with the whole database and the fields are more generic.

•	In the case of analyses, the entity type, the point in the analysis at which the Calculation Editor has been invoked and if user defined fields have been added, will make different fields available.

It is recommended that available fields are double clicked to insert them into the formula box, to ensure that they are spelt correctly, surrounded by braces, etc.

A wide range of functions is available, with the required syntax displayed beside each, eg AvgX(expr, expr, ...).

The functions are organised within folders, eg Mathematical, String etc. Each of the functions in the Calculation Editor, together with its syntax and example usage, may be accessed by clicking here.

Functions may be typed in or double clicked to insert them into the formula pane.

The right most pane contains all of the operators that may be used when building a calculation, eg =, OR, &, etc. These are also grouped in folders, based on their type, eg Mathematical, Logical, etc.

These may be typed in or inserted from the Operators pane by double clicking.

When a specific item is clicked in the Fields, Functions and Operators panes, a brief description of the item is displayed below the three panes. For example, above, the Division operator has been clicked and the description informs you of its usage.

The three panes in the bottom part of the Calculation Editor all contain hierarchical folder structures from which you may draw fields, functions and operators. The following may be used to navigate and make selections from any of these panes:

•	Open and close a folder to reveal the available fields, functions or operators by:

➢	Clicking the arrowhead beside the folder to open and close respectively.

➢	Double clicking the folder or its text.

➢	Using à and ß keys to open and close folders.

•	Use á and â to move up and down through the folders and and their fields, functions and operators.

•	Insert a highlighted field, function or operator into the calculation pane by:

➢	Double clicking it.

➢	Pressing Enter.

➢	Pressing the space bar.

Once you have activated the Calculation Editor window you may define an expression using the correct syntax by either:

•	Typing the formula directly into the formula box at the top of the Calculation Editor, ensuring the correct syntax, function names and arguments, operators and available fields are used.

•	Compose the formula by inserting its various components from the Fields, Functions and Operators panes, ensuring that the mouse pointer is positioned in the formula box at the point you wish to insert a field, function or operator:

➢	Insert the required field from the Fields pane, by either double clicking the field or pressing Enter when it is highlighted.

The field will be inserted in the formula box with a set of curly brackets around it, eg {Student.Family Name}.

➢	Insert the appropriate function from the Functions pane, in the required position, by either double clicking it or pressing Enter when it is highlighted.

The function will be inserted with a set of parentheses following the function name, eg DCountIf( ). The mouse pointer will be positioned within these parentheses and you must type in the correct arguments for the selected function.

➢	Insert an operator from the Operators pane, in the required position, by either double clicking it or pressing Enter when it is highlighted.

•	Ensure the formula has the correct syntax, matching sets of parentheses, etc.

•	Ensure that the formula has been composed so that its components are evaluated in the correct order required by you, ie in accordance with the precedence rules.

•	Click the OK button.

•	When there are errors in a formula when in a subject or class results window, error text will be displayed in the results cells, eg #MAX!, #VALUE!, with each of these having a specific meaning.

AI CalcFunctionsErros

Calculations in Assessment Items