Formulas in Spreadsheet Documents
- 3 minutes to read
A spreadsheet formula is an equation that performs a calculation on the numbers, functions, and values of one or more cells. A formula is associated with a cell or a cell range. The CellRange.Formula property obtains the formula.
Formula Types
| Formula Type | Description | Related Topic |
|---|---|---|
| Normal | Use the CellRange.Formula property to assign a formula to a cell or to each cell in a range. | Create Formulas |
| Shared | Shared formula is created automatically when required without user intervention when you assign a formula string to an array of cells. A specified formula is associated with each cell contained within the specified cell range. Shared formulas are used internally to optimize calculations and file size. Refer to the document for more information. | How to: Create Shared Formulas |
| Array | An array formula executes operations on one or more sets of values, known as array arguments. Each array argument must contain the same number of rows and columns. The outcome of an array formula can be either a single value or multiple values. Two types of array formulas are available: static (legacy) and dynamic. | Array Formulas |
Formula Syntax
A formula is a string expression that begins with an equal (=) sign. A formula can contain the constants, operators, cell references, calls to functions, and names.
Consider the following formula, which calculates the mass of a sphere.
=4/3PI()(A2^3)*Density
- “4” and “3” are numeric constants. Although they are written as integers, the division operator (/) interprets them as being real numbers, that is, 4.0 and 3.0. As a result, the calculation produces a precise result that is not rounded to an integer.
- “/” is the division operator.
- “PI()” results in a call to the PI function, which returns the value of π.
- “A2” is a cell reference, which returns the value within that specific cell.
- “3” is a numeric constant.
- “^” is the caret operator, which raises the left operand to the power of the right operand.
- Parentheses are used for grouping and changing the operator precedence.
- “” is the asterisk () operator, which performs multiplication.
- The Density is a defined name within the worksheet that define a cell range, a function or a constant.
The formula is calculated from left to right, according to the operator precedence. To change the order of calculation you can enclose a portion of the formula in parentheses.
Parts of a Formula
Calculation
To recalculate all formulas in a workbook, call the IWorkbook.Calculate method. The DocumentSettings.Calculation property provides access to calculation options. Calculation results are placed in the CellRange.Value property of corresponding cells. See the Calculation Process topic for more information.
You can also calculate a formula and leave the document unchanged by using the IWorkbook.Evaluate method.
Formula Engine
The FormulaEngine is an object that provides the capability to calculate and parse worksheet formulas. It includes a built-in formula parser, as well as the flexibility to evaluate formulas in any range of any worksheet. See the Formula Engine topic for more information.