To sum a set of items that exist as a group in the lines or columns, you can use the colon as a range operator ( : ) to define the range. To define a range, you will reference the Code in the Line or Column Definition’s Code field.
Any single line formula that uses a range must represent contiguous values in the line or column set. To build complex or multi-line calculations for multiple ranges, use the Calculations window from the button in the Line Definition’s Formula field.
Command Syntax:
[CODE1]:[CODE2]
Where CODE1 is the CODE used on the first row of the range and CODE2 is the CODE used on the last row in the range.
Similarly, to use a range in a calculation on columns, the format is:
[CODE1]:[CODE2]
Where CODE1 is the CODE used on the first column of the range and CODE2 is the CODE used on the last column in the range.
Prospero will automatically convert the range into a summation of all of the rows or columns between the first and last code referenced in the range.
Note: When column values are suppressed using the Suppress option, calculations for lines or columns will skip the hidden columns’ values, depending on if the specific operators are defined in the formula or a range is used. This does not require you to modify your calculations only that you note that the use of specific operators versus ranges is different when Suppress is used.
For example, if you use specific operators, the hidden columns will be used as part of the calculation. If you use a range in the formula for the operators, the hidden columns will not be used as part of the calculation. Therefore, where columns are coded as A, B, C, D - and columns B & C are suppressed - calculations are interpreted as follows:
|
Entered formula |
Interpreted formula |
|
[A]:[D] |
A+D |
|
[A]+[B]+[C]+[D] |
A+B+C+D |