Using Formulas in a Report

Decimal places are displayed in Dynamic Reports based on 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.

Note:

Select a range of cells to view the Average, Count, Numerical Count, Min, Max, and Sum in the status bar.

Formula

The formula contains the following options:

1. Insert Row/Column: Use the Insert Row/Column option to add a formula row or column.
    

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

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

   • Function: Select the type of function. For example, if you want the sum the total of a column, select this function. The available functions are:

     • % 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.

2. Edit Row/Column: Select the inserted row or column added in Reports using the Insert Row/Column option, and click this option to update the changes.

3. Delete Row/Column: Select the inserted row or column added in Reports using the Insert Row/Column option, and click this option to delete the row or column.

4. Formula Exceptions: 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.

5. Show/Hide Formula Bar: Click this option to view or hide the formula bar.