Add a formula

Formula ranges are used to include formulas into templates. They automatically expand to cover the whole period included into a report or a project. You can add as many formula ranges as necessary.

Note: Excel formulas are not automatically expanded when the report is populated, so instead you must define a formula range and place the formulas in the correct cells.

To add a range:

  1. Click on The Link Result Variables button on the Palantir tab on the ribbon. The Link report template variables window will be displayed.
  2. In the Settings section, expand Others and select the Formula Ranges option.
  3. The Formula Ranges section contains the list of existing formula ranges, buttons to manage ranges, and the field used to add new ranges. The image below shows several formula ranges created in a template.

The Formula Ranges option

  1. Click on The Range Finder button in the Formula Ranges section. The Link report template variables window will be collapsed and the template will be displayed.
  2. In Excel, select the cell where you want the formula range to start. The cell name will appear in the collapsed window as shown below.

Selected cell name

  1. Click on The Range Finder button in the collapsed window. The window will be expanded and the selected cell's name will appear in the Formula Ranges section.

The new formula range

  1. Click on the Add button. The cell range will appear on the list of ranges.
  2. Click OK to save the link and close the Link report template variables window.
  3. The linked range will appear in the report template. It consists of two placeholders: <START FORMULA> and <END FORMULA>. When the report is run, placeholders will be replaced with the calculated values.
  4. Enter the formula in the <START FORMULA> and <END FORMULA> cells. Type test values into the template to make sure the formula produces correct results.
  5. Format the formula cells and enter an appropriate heading.

To delete a linked range, select it in the text box and click on the Remove button.

To link an existing range to different cells:

  1. Select the range in the text box.
  2. Click on The Range Finder button in the Formula Ranges section.
  3. The Link report template variables window will be collapsed and the report template will be displayed.
  4. In Excel, select a new starting cell for the range. The cell name will appear in the collapsed window.
  5. Click on The Range Finder button in the collapsed window. The window will be expanded and the new cell name will appear in the Formula Ranges section.
  6. Click on the Change button. The existing range will be replaced with the new one.