Formulas

<< Click to Display Table of Contents >>

Navigation:  Writing the Report >

Formulas

Previous pageReturn to chapter overviewNext page

Formulas allow you to do basic math (addition, subtraction, multiplication and division) on numbers that are in a table. These can be cells that contain only numbers or cells including both words and numbers. You can also use a conditional expression to compare values and fill a cell with something that describes the result of that comparison.

 

To insert a formula, first open the topic you would like to add the formula to.  If a table has not already been created, create one. Place your cursor where you would like to insert the formula.  Next click the Formula button Formula on the toolbar which launches the Formula window described below:

 

FormulaDialog

 

Formula Field – This where the formula expression is entered or modified. Delete an existing formula expression by clearing out this field.

 

Decimal Places – Use the dropdown to select the number of decimal places for the result of the formula expression.

 

Formula Options – Select one of these two options, depending on how you want the formula to behave.

Insert of update the formula as an inline text object
Useful if the formula will be in a paragraph with other text; however, the result of the calculation will not be able to be referenced in another formula.

Replace or update the active paragraph with the formula
Useful if the formula is going into a table cell by itself and the result of the calculation needs to be referenced in another formula.

 

Usage

If names were assigned to one or more paragraphs, the formula can consist of simply the name of those paragraphs.  In such cases, all paragraphs having the name specified in the formula will be added together.  To add a name to a paragraph, select one or more paragraphs and right click, then select Paragraph > Name from the pop up menu.  There are two different types of formula expressions in WhisperReporter Ascent: Expressions that use “named paragraphs” (Advanced) and those that do not (Simple).

 

Expressions

left(N, N2,...Nn)

Sum up the cells to the left where Nx is the offset from current cell

right(N, N2, ... Nn)

Sum up the cells to the right where Nx is the offset from current cell

previous(N, N2, ... Nn)

prior(N, N2, ... Nn)

Sum up the cells in the same column but previous (prior) rows where Nx is the offset from current cell

average(name)

Calculate the average of all paragraphs with the given name

valcount(name)

Count the paragraphs with the given name

if expression,trueValue,falseValue

Conditional expression. The expression can use any of the above expressions in a formula.  If the expression result is true, use trueValue in the cell, else use falseValue in the cell.  The values can be string or numeric types.  Expression can use various logical operands such as < (less than), > (greater than), <= (less than or equal), >= (greater than or equal), <> (not equal), != (not equal), & (logical and), | (logical or)

 

Examples (see Simple Formula Expressions and Advanced Formula Expressions for more info)

left(1,2)   -or-   left(2)+left(1)

Adds Column 1 to Column two and places the result in column 3

(left(2) * left(1)) +  ((left(2) * left(1)) * 0.5)

Multiplies Column 1 by Column 2 and adds 0.5% and places the result in column 3

previous(1,2,3,4)

Totals a 4 row column and puts the result in row 5

total * 0.08

Totals all paragraphs or cells that were named "total" and computes the sales tax

if output < input, restricted flow, unrestricted flow

If a cell value is less than another cell value, show something to indicate that

 

See also:

Simple Formula Expressions

Advanced Formula Expressions