Using Formulas
- 5 minutes to read
Data storage and representation is not the only purpose of spreadsheet documents. As with Microsoft Excel®, performing calculations on cell data by using various formula expressions is the prominent feature of the Spreadsheet control.
For more information, refer to one of following sections:
Formula Expression Concept
Constructing a Formula
Referring to Cells
Using Functions
The Formula Expression Concept
A formula expression (a formula, for short) is an equation that carries out specific operations against worksheet data. End-users enter formulas into a worksheet similar to entering data, by using an in-place cell editor. The calculation result is displayed in the cell that contains a formula when an in-place cell editor is closed, provided the OptionsBehavior.AutomaticCalculation property is set to True. Otherwise, you need to call the Spreadsheet control’s FormulaController.Calculate procedure or press F9 to see the calculation result.
A formula expression can contain cell references, arithmetic operators, and functions designed for specific tasks, which include but are not limited to: statistical and financial analysis, data lookup, and logical operations. To see the full list of supported operators and functions supported by the Spreadsheet control out of the box, refer to the Built-in Operators and Functions topic. If none of the predefined functions suits your needs, you can create your own functions and use them in the same manner.
The formula example below adds 600 to the value within the A1 cell and then divides the result by the sum (returned by the SUM function from the arithmetic functions set) of the values within the C1 and D1 cells.
Constructing a Formula
The formula results are calculated according to the order of operations. Like in Excel, a formula in the Spreadsheet control always begins with an equals sign (=), which indicates that the subsequent characters in this string should be treated as formula. The equals sign can be followed by constants, math operators, cell references, and functions. The Spreadsheet control calculates formulas according to the order of operations, i.e., from left to right, with multiplication and division applied before addition and subtraction while the exponents have precedence over all abovementioned operations. To change the order of operations, you can use parentheses. As a result, any expression within brackets is calculated first.
Note
The symbol, used to separate arguments and parameters in the Spreadsheet control, depends on local and regional settings. If DecimalSeparator and ListSeparator provide the same values, a semicolon is used as a separator.
Cell References
Formulas use both constants and cell references as their operands. Additionally, you can create and use defined names as formula expression operands. A formula can refer to other cells which can be located both within the same worksheet or others, including sheets from other spreadsheet documents.
The cell that contains a formula is known as a dependent cell since its value depends on the values of other cells. For example, the B2 cell is a dependent cell if it contains the =C2 formula. Thus, the contents of the B2 cell is updated with the contents of the C2 cell every time it changes.
The Spreadsheet control supports two types of cell addressing:
Relative addressing that assumes that if you delete a column, all addresses will be changed accordingly, its designation is ‘=C2’ (or ‘=R2C3’ if the Spreadsheet control’s OptionsView.R1C1Reference property is set to True). So, if the column A is deleted, this address changes to ‘=B2’ (or ‘=R2C2’);
Absolute addressing does not change when a column is deleted, its designation is =$C$2 (or ‘=R[-2]C[-3]’ if the OptionsView.R1C1Reference property is set to True).’
Whenever the cell to which a formula expression refers changes, the dependent cell also changes by default. If you enter a formula manually, the OptionsBehavior.AutomaticCalculation property must be True in order to enable the formula calculation. Otherwise, you need to call the FormulaController.Calculate procedure or press the F9 key.
If you use constant values instead of references to cells (for example, ‘= 30 + 70 + 110’), the result changes only if you modify your formula or force recalculation by calling the FormulaContoller.Calculate procedure.
Using the Functions
The Spreadsheet control contains a number of predefined or built-in functions for use in formula expressions. These functions can be used to perform both simple and complex calculations. The SUM function, which is used to add numbers in a cell range, is one of the most frequently used functions. The image below shows how to use the SUM function to add values in the C1:D1 range.
Most of the supported built-in functions can be used in array formulas.
If necessary, you can expand the built-in functions list by implementing your own functions. Once new functions are implemented and registered within the Spreadsheet control, they can be safely used in the same manner as the functions from the predefined list. For more information on custom functions, refer to the How to Create Custom Functions topic.