Using Formulas

A constant is a ready (not calculated) value. Examples of constants: date 10.10.2015, number 210 and text "Quarterly profit". Expression or its value are not constants.

If a formula does not contain references to other cells (for example, a formula looks as =30+70+110), value in such cell is changed only after manual changing of the formula.

Operators are operations that must be executed on formula operands. There is a standard order of calculation execution but it can be changed by means of brackets.

On creating formulas in regular reports it is possible to use various types of operators: arithmetic operators, comparison operators.

Arithmetic Operators

They are used to execute arithmetic operations: addition, subtraction, multiplication. Operations are executed on numbers. The following arithmetic operators are used:

Operator	Description
+	Addition.
-	Subtraction.
*	Multiplication.
/	Division.
DIV	Integer division.
MOD	Remainder of the integer division.

Comparison Operators

They are used to compare two values. The result of comparison is a logical value: True or False.

Operator	Description
=	Equal.
<	Less.
>	Greater.
<=	Less or equal.
>=	Greater or equal.
<>	Greater or less (not equal).

Special features of using references to cells of report sheet in a formula:

• Single cell. Specify address of a sheet cell, such as B8.

• Column. To use all cells of a column in a formula, specify its name via a colon, for example: B:B. To use all cells of several columns, specify their name via a colon, for example: A:C.

• Row. To use all cells of the same row, specify its number via a colon, for example: 5:5. To use all cells of several rows, specify their name via a colon, for example: 1:5.

• Cell range. To use a cell range in a formula, first specify address of the upper left cell, where the range starts, next, after a colon, specify address of the bottom right cell where the range ends, for example:

C7:D15

Where:

• C7. The address of the upper left cell.

• D15. The address of the lower right cell.

• Combined cell range. To describe an area that contains several not intersecting ranges (including single cells), specify addresses of rectangular areas and individual cells, separating them with a semicolon (;). For example:

C7:D15;A1:B4;B8

Where:

• C7:D15 and A1:B4. Cell ranges.

• B8. Single cell.

• Referring to the cells of another sheet. To use a cell (or a cell range) located on another sheet, specify sheet name in quotes and with an exclamation mark before the range.

A link to a sheet is inserted following these steps:

1. • The sheet name is inserted into cursor position when the user moves to another sheet. If a term was selected, sheet name is inserted into selected term position. No objects are selected on the active sheet.

   • Select the required range on the sheet - references to a range are inserted into cursor position. The range is selected with a dashed frame.

   • Changing the active sheet adjusts sheet name in the resulting term, the reference to range is not adjusted, it corresponds to selection on each of the activated sheets. Changing borders of the range selected on the active sheet results in adjusting the reference to this range in the resulting term.

   • When the active sheets changes, the cursor remains within the cell editor or in the formula bar, depending on its position before this event.

   • If the formula is entered with a cell editor, and another sheet is activated or a term is inserted on entering the formula, when the user goes back to the current sheet, the editor is restored, and the cursor is placed after the added term.

   • After finishing to enter formula, the sheet, to which the formula was entered, becomes active.

Formula editing stops when another sheet is activated if:

• The cursor is positioned after any term.

• The cursor is within a term.

• Several characters that are not a term are selected: a cell address (A0) and a sheet name with the exclamation mark (“Sheet1”!).

If other sheets are activated when the formula is being entered, the current sheet is highlighted. References to other sheets in formula or on a sheet are not highlighted in color. Examples of references:

="Sheet1"!A3

The A3 cell value from the sheet 1.

=Sum("Sheet2"!A:A)

The A column value sum from the sheet 2.

=Sum("Sheet3"!C31;A31;E31)

Sum of the combined range: the C31 value is taken from the sheet 3, while A31 and E31 values are taken from the current sheet.

=Sum("Sheet2"!B31:D31)

Sum of value range from the sheet 2.

To specify cell or range references, use the mouse or type the address using the keyboard provided that the first character in the cell should be the equality sign. To specify the combined range of cells using the mouse hold the CTRL key.

To specify addresses using cursor keys, follow this procedure:

1. Enable the input mode:

• Start entering text for the selected cell with the equality sign.

• Double-click on an empty cell.

• Press F2.

2. Enter the equality sign (if there is not any).

3. Using cursor keys, select the required cell (its address changes in the formula at the moment of selection), and after that continue entering the formula. Hold down the SHIFT key to select a continuous value range.

NOTE. Therefore, cells can also be selected after entering the characters: multiplication *, division /, opening bracket (, comma ,.

The selected cell (or range) is highlighted using a dotted color frame. When entering operators or signs separating function parameters, a cell is fixed and highlighted using solid color frame. The next mouse click (or clicking a cursor key) specifies the address of another cell. Use the mouse to move, increase or decrease a highlighted cell or range, the formula address automatically changes. To enlarge or reduce a range, point the cursor to cell corner and after it looks as a double-headed arrow drag the range border in the required direction.

Input or edited cell (range) address and the appropriate cell (range) borders have the same color.

NOTE. If it is required to use circular references in formulas, enable the iterative calculations option in the report parameters. By default, such formulas are not calculated, and the cells display the following error message: "Circular references are found". Examples of circular references: the =C11+ B9 + A0 formula is defined for the A0 cell; and the following formulas are defined for three cells: A0 (=A1 + 1), A1 (=A2 + 1), A2 (=A0 + 1).

Two reference styles can be used when creating references:

• A1. When A1 style is used, columns are marked with letters and rows are marked with numbers.

• R1C1. On using the R1C1 style, rows and columns are marked with numbers, and references for cells in formulas are set by shifting from the current cell. For example, the cell R[-1]C[2] refers to the cell one row to the top and two columns to the right of the current cell.

It is available to use mixed styles in formulas as it does not affect calculation results.

Example:

=$A$0+R[0]C[-1]

On copying this link the value of a cell located in the row where the link has been copied to and the column on the left from the cell where the link has been copied to is added to the value of A0 cell.

The reference style to be used for auto composition of formulas is determined in the settings of the regular report.

NOTE. References to columns and rows are always created using A1 style. During export the links with R1C1 style are transformed into A1 style.

On creating formulas the following reference types can be used:

• Relative reference in a formula, for example, A1, is based on the position of the cell that contains the formula, and the cell, to which the formula refers. On changing position of the cell that contains the formula, the reference is changed too. On copying or filling a formula along rows and columns the reference is automatically corrected.

• Absolute reference of a cell in a formula, for example, $A$1, always refers to the cell located on a certain sheet. On changing position of the cell that contains the formula, the absolute reference does not change. On copying or filling the formula along rows and columns the absolute reference is not corrected.

• Mixed reference contains either an absolute column and a relative row, or an absolute row and a relative column. On changing position of the cell that contains the formula, the relative reference is changed and the absolute reference is not changed. On copying or filling the formula along rows and columns the relative reference is automatically corrected and the absolute reference is not corrected.

Depending on reference style the way of indicating absolute and relative references differs:

• Style A1. If the A1 style is used for specifying absolute references, use the $ sign. The $ sign is inserted before the reference part to be fixed:

=$A1

On copying this reference the column A is always fixed. Relative part of the reference, that is, row number changes according to the row, to which the reference is copied (=$A2, =$A3, and so on).

• Style R1C1. On using the R1C1 style, to specify absolute references it is required to specify number of the row or column after the R or C letters. Relative numbers of rows and columns are indicated in square brackets and correspond to shift relative to the current cell. When this reference is copied, the relative number does not change, but in fact it corresponds to another row or column.

=R[-3]C0

When copied, this reference has the same appearance, but on calculation the reference corresponds to the cell positioned in the first column three rows above the formula cell.

Features of Reference Use

• The =$A$0 absolute reference created with the use of the A1 style is identical to the =R0C0 reference created with the use of the R1C1 style.

• The =RC1 reference created with the use of the R1C1 style is identical to reference to the cell located in the RC column and in the first row created with the use of the A1 style.

Functions are previously defined formulas that calculate by the specified values named arguments in the specified order. Functions enable the user to execute both simple and complex calculations.

To add a built-in function to formula, open the function wizard or enter function name and parameters to the cell.

The following categories of functions are available:

• Mathematical.

• References and arrays.

• Date and time.

• Text.

• Working with a report.

• Financial.

• Statistical.

• Logical

• Python;

• Java.

Some functions return array of values (for example, MInverse, MMult), so they are to be entered as array formulas. To enter an array formula:

1. Enter functions to a cell by using function wizard or keyboard.

2. Select a cell range (array) where the calculation result is to be placed, starting from the cell that contains formula.

3. Press F2, and then CTRL+SHIFT+ENTER.

After executing these operations the area shows the array of values returned by the function.

If an area larger then returned array has been selected (step 2), the spare cells display #N/A value; if a too small area has been selected only a part of the array is to be displayed.

NOTE. To delete the array formula, select a cell range that contains the formula. An error message appears if the user tries to edit or delete formula in the part of the array.

Connect the development environment object containing implementation of the required function. Public access modifier must be used for the formula.

To use the Fore function in a formula, specify the expression in the cell after the = character: <Unit identifier>.<Function name>[(<Function parameter values>)].

For example, custom function in unit with the MODULE identifier looks as follows:

Public Function My(Value: Integer): Integer;
Begin
    Value := Value + 50;
    Return Value;
End Function My;

To use the custom function, enter the expression into the selected cell:

=MODULE.My(50)

After the calculation the selected cell shows the value 100.

Custom functions can be used to enhance regular report capabilities due to the use of custom calculation methods.

Using properties of the PrxReport and MetabaseClass classes, it is available to display some information in the cells. For example:

• Use the following function to show the first data source name in the report cell:

=PrxReport.ActiveReport.DataSources.Item(0).Name

• Use the following function to show the current data source name in the report cell:

=MetabaseClass.Active.Name

A formula can be created in the following ways:

• Directly in the cell. To do this, execute the following operations in the cell to edit:

1. Enter the equality sign. Skip this step if the cell already contains this sign.

2. Create a formula using numbers, statements, links to cells and functions.

3. Press the ENTER key to save and calculate the formula.

• In the formula bar.

• By using the function wizard. Execute one of the following operations:

• Click the button in the formula bar.

• Click the Insert Function button on the Formulas ribbon tab.

• By copying the cell with the created formula. Execute the following operations:

1. Copy the cell with formula.

2. Select the Paste Special item context menu item of the cell, to which a formula must be added. Select the Cell Formulas checkbox.

3. Click the OK button.

To cancel entering formula, click:

• The ESC key.

• The button.

To set up formula calculation parameters, go to the Formulas tab of the Report Parameters dialog box.

A formula can be edited in the following ways:

• Directly in the cell. To do this, in the edited cell:

1. Edit the formula using numbers, statements, references to cells and functions.

2. Press the ENTER key to save and calculate the formula.

• In the formula bar.

• By using the function wizard. Execute one of the following operations:

• Click the button in the formula bar.

• Click the Insert Function button on the Formulas ribbon tab.

To cancel entering formula, click:

• The ESC key.

• The button.

To set up indication for cells that contain error values, select the Tools > Parameters main menu item. Go to the Formulas tab of the Report Parameters dialog box and select the Error Checking checkbox.

NOTE. If multiple error cells with errors are selected, the error identifier is shown for the upper left range cell. Only the Skip Error option is available for multiple cells.