Creating Formulas and Performing Calculations
- 4 minutes to read
This topic describes how to accomplish formula-related tasks in the ExpressSpreadSheet control. For theoretical information on formulas, refer to the Formulas Overview topic.
Several common questions concerning formulas include:
Entering Formulas;
Editing Formulas;
Using References;
Using Functions.
Entering Formulas
A formula in the ExpressSpreadSheet always begins with an equals sign (=). The equals sign tells the parser that the subsequent characters should be treated as a formula. As a result, the elements following the equals sign will be calculated as operands, which are separated by calculation operators. Like Excel, the ExpressSpreadSheet calculates formulas from left to right, based upon the specific order for each operator in the formula.
To enter a new formula into a cell, an end-user should follow this sequence of actions:
Click required cell;
Enter ‘=’ (an equals sign);
Enter the formula;
Press Enter.
In order to enter a formula programmatically, you should use the SetText method of the TdxSpreadSheetCell object corresponding to the required cell.
The following code example enters =A1 + B2 / E3 into the C3 cell.
var
ACell: TdxSpreadSheetCell;
begin
ACell := dxSpreadSheet1.ActiveSheetAsTable.CreateCell(2, 2);
ACell.SetText('= A1 + B2 / D1', True);
end;
Editing Formulas
End-users can double click the cell containing the target formula or press F2 to invoke the in-place editor, modify the formula, and then press Enter to apply changes. The formula references can be adjusted either by using the cell editor or by performing drag-and-drop operations on the highlighted referenced areas.
If you need to change the formula programmatically, simply assign the new formula via the SetText method of the TdxSpreadSheetCell object. Refer to the code example in the Entering Formulas subtopic.
Using References
A formula can refer to constant values and to contents of other cells. The cell that contains a formula is known as a dependent cell since its value depends on 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.
You can also use the special reference operator ‘:’ (colon) to specify a range of cells. For instance, the A1:A4 range denotes the A1, A2, A3, and A4 cells. The A1:B2 range denotes the A1, A2, B1, and B2 cells. Ranges are used in arithmetic functions involving values of multiple neighboring cells as operands.
Like in Excel, the referenced cells and cell ranges are highlighted by the colored rectangles in the formula edit mode. The references within formulas have the same colors as the rectangles that highlight the respective cells and cell ranges:
An end-user can modify these references by performing drag-and-drop operations on the respective highlighted areas. Dragging the corners and borders of the referenced areas resizes and moves them, respectively.
Using Functions
The ExpressSpreadSheet contains a number of predefined built-in functions. They allow you to perform arithmetic, lookup, logical operations, perform statistical and financial calculations, operate with date/time values, and more. The full list of predefined functions available in the Spreadsheet control is provided in the Built-in Functions topic. Additionally, you can implement and add your own functions to this list. Refer to the How to Create Custom Functions for details.
Functions can be combined by operators and passed as arguments to other functions.
For instance, in order to calculate the square of sum of values in the A1:A4 range, you can type the formula into a cell using the predefined SUM function:
=SUM(A1:A4)^2
Combined functions can also be entered into the cells programmatically in the same way as it is shown in the Entering Formulas subtopic.