Formulas

Jun 17, 2021
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. It is accessed using the CellRange.Formula property.

Formula Types

Formula Type	Description
Shared	A shared formula can be used to optimize calculations and file size. A shared formula is the equivalent of applying the same formula to a range of cells. Shared formula is created automatically 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.
Array	An array formula is a special kind of formula used to perform calculations with arrays of cells. Use the CellRange.ArrayFormula property or ArrayFormulaCollection.Add methods to include array formulas in your worksheet. An array formula for a cell or a range is accessible via the CellRange.ArrayFormula property. To find an array formula range that includes a particular cell, use the Cell.GetArrayFormulaRange method. For more information on array formulas, see the Array Formulas document.

Formula Type

Description

Shared

A shared formula can be used to optimize calculations and file size. A shared formula is the equivalent of applying the same formula to a range of cells. Shared formula is created automatically 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.

Array

An array formula is a special kind of formula used to perform calculations with arrays of cells. Use the CellRange.ArrayFormula property or ArrayFormulaCollection.Add methods to include array formulas in your worksheet. An array formula for a cell or a range is accessible via the CellRange.ArrayFormula property. To find an array formula range that includes a particular cell, use the Cell.GetArrayFormulaRange method. For more information on array formulas, see the Array Formulas document.

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/3_PI()_(A2^3)*Density

“4” and “3” are numeric constants. Although they are written as integers, the division operator (/) interprets them as being real numbers, i.e., 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 can represent 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.

You can also calculate a formula and leave the document unchanged by using the IWorkbook.Evaluate method.