Using a Formula - Example

Example I

To use a Formula object, drag it from the toolbar and drop it into the required place in the table, then double-click on the object to open its properties page.

Figure 1 - A Formula object added to a table

In the example . assume we want the table to calculate the difference between the Male and Female results. The settings on the Formula object define the arithmetic operator and the arguments of the arithmetic operation .

1. Select the Type of formula you wish to use (go to Type for more information). In this case, as the calculation is simple, we will use Operators.
2. Select the required operator, in this case "subtraction".

The four standard arithmetic operators are available: Subtraction (-), addition (+), division (/) and multiplication (*).

3. Select a reference type. We will use Relative.
• Relative means that the arguments will be found by counting columns or rows from the Formula's position in the table.
• From start means that you specify the arguments by counting columns or rows from the beginning of the table.
• From end means that you specify the arguments by counting columns or rows from the end of the table, moving towards the beginning of the table.
4. Define the left and right Arguments, based on the Reference Type selected. For this example, the Arguments are -2 and -1 respectively.

Negative numbers indicate a number of columns or rows before the one containing the formula; positive numbers indicate a number of columns or rows after the formula.

5. Type a header text for the column into the Label fields, for each language; in this case, Gap is used.

If a percentage value is required, select the Percent checkbox.

Note: Depending on the type of calculation performed, the "percentage" value may not be logical/useful. For example, if the result of a subtraction operation is 15 and you check the Percentage box, then the value presented will be 1500%. However if the result from a division calculation is 0.17 and you want the result as a percentage, check the Percent box to present the result as 17%.

The properties should look as below:



Figure 2 - Formula properties

6. Click Apply.

The table should look as below .

Figure 3 - Example of the formula element in use

Example II

A case may arise where you need to set all the values in a column in an aggregated table. For example, you may need to create a column to hold a weighting value specific for each row. This can be achieved using a formula. The example below .- shows a table where the third column has been preset with the values 5.6, 3.4, 45 and 456.

Figure 4 - Setting the values in a column

To do this:

1. Create a Formula object in the table (go to About Formulas for more information).
2. Double-click on the Formula object to open its Properties page.
3. Set Type to Expression, then type the following expression into the Expression field:

IF(row=1, 5.6, IF(row=2, 3.4, IF(row=3, 45, IF(row=4, 456, CELLVALUE(col,4)))))

There must be one IF condition for each data row in the table that is to have a specified value. Any rows not specifically stated in the condition will be given the value specified by the ELSE part of the condition CELLVALUE(col,4). In this example, unspecified rows will be given the value specified for row 4 of the same column. You could for example state that unspecified rows be given the value specified for row 5 of the previous column. In this case the ELSE part of the condition would read CELLVALUE(col-1,5), and the value given to all unspecified cells would then be 12.

The data rows in the table are numbered consecutively, starting from 1 for the uppermost data row. (Note that this table has 1, 2, 3 etc. in the first column - this is coincidental.) When the IF condition is run, the system will read through the condition to check whether a value is specified for the first row in the table. If so, then that value will be added to the cell; if not, then the value specified by the ELSE part will be added. The system will then look for a value for the second row in the table, and so on.

Note: This solution will not work if you use Hide Empty Headers or Masking in the table.