Calculations

Data entry and supporting tools

In addition to importing data, changes can definitely be made directly in the Excel QRT cells.

Calculation cells
The cells that can be changed are the white cells. The coloured cells are calculated cells which cannot, by default, be changed:

Subtotal cells
The purple cells are those with a formula bringing in data points from the same QRT.

Reference cells
The orange cells are those with a formula from the Tabular roll-forward feature. These formulas will always include at least one value from the H.HV sheet (but may also include values from other sheets eg in S.29.01 where the formula calculates the difference between the current return value and the previous return value).

Interaction cell
The green cells are those with a formula with cells in another QRT.

In addition, when you create return Tabular tried to add a formula into every cell following this order:
1. It will try to add a subtotal.
2. If there is no subtotal available then it will try and add interaction
3. And finally if no interaction it will try and add reference.
As a result, some QRTs have a mix of different colours like s.29.01 for instance.

The calculated cells should automatically update. However please check that your calculation options in the Excel Formulas menu is set to automatic as per the screen below.

Removing/ replacing calculations
If a user so chooses, they can remove (and subsequently replace) any calculation from a cell, QRT, or Workbook.

This is done in the Validation section of the ribbon.

Select the type of calculation you wish to remove. For example, if a user wanted to remove the subtotal calculation from the selected cell:

Closed lists
Some data points are restricted to a closed list of possible values. Such cells are indicated with a light grey cell fill and entry into these cells is done through a drop down menu which holds the full list of possible values: