You are here

Multi-criteria Results table "Calculated columns"

Calculated columns serve for the calculation of indicator values based on rating results, the classic example being 'risk' which is often computed by multiplying the score on 'impact' with the rating on 'likelihood'.

Calculation can occur by

  • simple weighting i.e. multiplying a given table column with a given constant, the 'weight'
  • free formula i.e. computing values involving various operators, column values and constants

Calculated cells can be color coded with individual color schemes.

1 Add calculated column

To add a calculated column, click ADD COLUMN on the 'calculated column' tab.

  1. Name the column.
  2. Specify an explanatory subtitle if required.
  3. Move the column into place by dragging the column header into the desired position.

To edit or delete a calculated column, select that column with the 'select column' widget and click the 'Edit' or 'Delete'button that sits behind the widget. 

2 Weighted column

By default, ADD column creates a weighted column.

If that's what you want, specify

  1. The base column i.e. the column whose values shall be weighted
  2. The 'weight' i.e. the constant factor by which the values of the base column shall be multiplied
  3. The decimals (default 2) of the weighted value in the new column

Example: Value of base column = 10; Weight = 0,375; Decimals = 2 will produce a weighted value of 3.75

Tip: Often, after having added a weighted column, the base column is no longer required for analysis. If so, hide the base column by setting its 'Show' switch to OFF.

3 Free formula

If simple weighting is not good enough, enable entry of a "Free formula" by that switch.

Free formula ON enables the functionality required for composing a free formula that involves 

  1. Columns
    i.e. already existing columns which can be either 'results columns' or 'calculated columns'. Columns whose use would produce a circular reference are grayed out.
  2. Constants
    i.e. a fixed numerical value (Default value = -1.0)
  3. Operators
    i.e. + , , * , / , ( , )

Formulas are built from left to right by clicking on what comes next in the staging area below the formula field. Since the rules of arithmetic apply, you cannot add any element at any time. For example,

  • If the formula field is empty, you can add an 'open' bracket '(' or a column or a constant but not +, -, *, / or ).
  • If the last element of the formula is a column or constant you can add a 'close' bracket ')' or operators +, -, * or / but not another column or constant or '('
  • If the last element in the formula is +, -, *, / or 'open bracket' '(', you can only add a column or constant or another 'open bracket' '('    

and so on.

Constants require specification once you've added them to the formula. For negative values prefix a 'minus' with the keyboard. Note that negative constants or column values are automatically interpreted as 'in brackets' e.g. '-1.5' is interpreted as (-1.5).

You can edit your formula in three ways

  1. By deleting unwanted elements starting from the back (click on the 'X'), then adding the elements you want.
  2. By replacing elements by selecting another element that could sit in its place. For this, click the 'edit' button behind that element.
  3. By inserting brackets. For this select an element of the formula and click the relevant bracket in the staging area. If the selected element is
    • a column or constant, '(' is inserted before, ')' is inserted after
    • +, -, * or /, ')' is inserted before, '(' is inserted after

You cannot edit or move brackets. Simply delete'X' the unwanted bracket and place a new one.

Brackets must come in pairs. 'Hanging' brackets, i.e. open brackets that lack a closing bracket or vice versa, are marked up in red. 

Disclaimer: Free formulas serve the purpose of real-time analysis with the group in the meeting. If you need more sophisticated calculations than supported by the formula editor, export the table to Excel via the toolbar's copy menu and do what you must in Excel while your participants enjoy a coffee break.

3 Color coding

Color coding of calculated columns works like the color coding of results columns except that it only applies individually to that calculated column meaning that you can apply

  • custom thresholds
  • a separate color scheme

to each calculated column.

Tip: You can use this feature also for specific color coding of rating results. For this, simply define a calculated column that equals the relevant results column (e.g. by simple weighting with factor 1), then hide the original (base) results column and color code the calculated column as required.