Using Formulas in a Report

Formula Exceptions 

Click Formula and select Formula Exceptions from within the Dynamic Report interface. Use formula exceptions to override the calculations performed on report results.

• Add formula exceptions by clicking on the Add Row… link.

• Once a new blank row is added, enter a cell or a range of cells and the formula that the exception will apply to. The range of cells must be entered as follows: E1:E26 . Formulas must start with an = (equal) symbol.

Formulas

Use the Quick Functions menu to select functions like Sum, Average, Count, Max, and Min. The resulting amounts are displayed when you select the range of cells in Excel mode.

Decimal places are displayed in Dynamic Reports based upon whether Report Sets are used or not. When Report Sets are not used in a Dynamic Report, decimal places displayed in quick functions are identical to those in the Dynamic Report. When Report Sets are used, decimal places in quick functions allow for the maximum number of decimal places allowed for a selected range.

A column or row with the Excel Formula function can be used to write advanced or complex Excel formulas in the report.

Each cell can have a different set of formulas.

From the top menu, click Formula and use the Insert Row/Column option to add a formula row or column.

Available Functions 

% of Row – Select a row and column intersection as the basis for percentage of all other rows on the spreadsheet. For example, select Column B, Row 3 which contains 100 dollars. And you want to insert a new column with % of Row function. A new column will be added based on the selections made on the Insert Formula page. The new column will result in 100% for Row 3 with 100 dollars. But, if Row 2 only contains 50 dollars. A percentage of 50% will be displayed.

Average – Average the amount of two rows or columns.

Comments – Add a row or column where you can add comments.

Count – Add a row or column that totals the count of columns or rows.

Max Value – Find the maximum value of a column or row.

Min Value - Find the minimum value of a column or row.

Q&A – Add a row or column where you can add questions and enter answers.

Column Total – Add a row or column that provides the summed amount for the selected row or column.

Sum – Find the sum or two rows or columns.

Variance – Find the difference between two rows or columns.

Variance % - Find the percentage of the difference between two rows or columns.

Custom Formula – Enter a row or column where you can create a custom formula to apply.

• Name – Enter a name for the formula you are inserting.

• Insert – Select the area of the report where the formula will be inserted.

• Function – For example, if you want the sum the total of a column, select this function.