User’s Guide
- User’s Guide
- Usage
- Functions
- Operators
- Cell References
- Working with Page Properties Report Macro
- Limitations
Usage
Place the fx Table macro on a page, and insert a standard Confluence table in the body of the macro. Then insert formulas directly in table cells, each formula must start with the “=” sign, which means evaluate this cell.
The Syntax for a formula is specified by:
=[number | text | fx(value, value, …)] [operator [number | text | fx( value, value … )]] …
Where:
- number is any set of digits representing a whole or decimal number or a cell reference
- text is any string contained in quotes
- operator is a valid operator that works with numbers or strings, refer to Operators below
- fx() represents a function call, refer to Functions below
- value can be either:
- Another function
- A cell reference
- A cell range
- A constant value
Here is an example page in Edit mode, utilizing the fx Table macro and formulas.
Functions
The following list of functions are currently available in the add-on.
If you need a spreadsheet function implemented, please raise a support ticket here: Service Desk
Operators
The following operators can be used in cell formulas
| Type of Operator | Operator | Meaning | Example |
|---|---|---|---|
| Arithmetic | + (plus sign) | Addition | =1 + 2 |
| – (minus sign) | SubtractionNegation | =6 – 1=-8 | |
| * (asterisk) | Multiplication | =3 * 3 | |
| / (forward slash) | Division | =3 / 3 | |
| % (percent sign) | Modulus (division remainder) | =10 % 3 | |
| String | & (ampersand sign) | Concatentate | =A1 & “ %” |
| Comparison | = (equal sign) | Equal to | =A1=B1 |
| > (greater than sign) | Greater than | =A1>B1 | |
| < (less than sign) | Less than | =A1<B1 | |
| >= (greater than or equal to sign) | Greater than or equal to | =A1>=B1 | |
| <= (less than or equal to sign) | Less than or equal to | =A1<=B1 | |
| <> (not equal to sign) | Not equal to | =A1<>B1 |
Cell References
Cell references are similar to any Spreadsheet, using a ‘LetterNumeral’ pair to identify the cell in the table. Each column is identified first by the Column, using a letter A – Z, and then by its Row number. For example, A1, which is always the top left cell of the table.
If the table has Heading rows or columns, these are also included in the reference, so cell A1 is always the first cell, even if table Headers are used.
There are some limitations currently for the Cell reference, these are:
- The maximum number of columns that can be referenced is 26, that is columns ‘A’ to ‘Z’ only. There is no cell AA1 for example.
- The maximum number of rows that can be referenced is 9999.
Complex Tables
To reference cells in complex tables, that is tables that have merged cells, use the reference for the first merged cell, then referencing skips merged cells until the next unmerged cell. This means that all columns should naturally fit underneath each other, and rows should naturally align.
The following table shows the cell referencing for a complex table.
Working with Page Properties Report Macro
When using the Page Properties macro and its partner Page Properties Report macro, an additional support macro has been provided for use when the formula result within the fx Table macro needs to be rendered in the report page containing the Page Properties Report macro.
This is a unique pair of macros, the Page Properties report can have any number of macros and tables in the macro body, it will process all the macro body as expected, including multiple tables. However, the Page Properties Report macro behaves very differently. It only processes the first non-header row or column of the first table in the Page Properties macro.
This means that the fx Table macro does NOT get executed when rendering the Page Properties Report macro, and therefore any formulas in the second row or column do not get evaluated, as expected, but just render the formula text.
To overcome this shortcoming, use the fx pp macro in the fx Table body. The fx pp macro simply wraps a formula and stores the result for use when rendering the Page Properties Report macro.
The fx pp macro should only ever be placed in the second row or column (depending on whether row or column headings are used) of the first table when used inside a Page Properties macro. The usage of this macro for any other purpose causes unnecessary processing and storage that will never be used.
Page Properties Macro Example
This example shows how to use the fx pp macro when evaluated data needs to be provided to the Page Properties Report macro.
Limitations
Cell Reference
- The last cell that can be referenced in the table is Z9999.
- Does not allow for the full column or row references, such as A:A, 1:1 will result in an error.
Cell Calculation Order
Each formula is calculated one by one, starting from the top left cell, working left to right, then top to bottom.
For this reason, if a formula in a cell is dependent on the result of another formula in another cell, then the dependent cell must be physically located in the confluence table preceding the current formula.
The following examples show how the dependencies will result in either the desired behavior or an error result.
Decimal Numbers
All decimal numbers, that have a fractional part will be rounded to 2 decimal places and only display 2 decimal digits after the decimal point.
If the decimal value is a whole number, then no decimal point will be displayed and the number will be shown as a whole number only.
KC Integrations 