# CellFormula Class

**Office 2010**

Formula.When the object is serialized out as xml, its qualified name is x:f.

DocumentFormat.OpenXml.OpenXmlElement

DocumentFormat.OpenXml.OpenXmlLeafElement

DocumentFormat.OpenXml.OpenXmlLeafTextElement

DocumentFormat.OpenXml.Spreadsheet.CellFormula

**Namespace:**DocumentFormat.OpenXml.Spreadsheet

**Assembly:**DocumentFormat.OpenXml (in DocumentFormat.OpenXml.dll)

[ISO/IEC 29500-1 1st Edition]

**18.3.1.40 f (Formula)**

Formula for the cell. The formula expression is contained in the character node of this element.

[*Example*:

<f>SUM(C4:E4)</f>

*end example*]

The possible values for the t attribute are defined by the simple type *ST_CellFormulaType*, and are as follows:

Value

Description

array (Array formula)

Array formula. An array formula is a single formula, applied across a range of one or more cells. An array formula can return multiple results from one single calculation, the results spanning the cells in which it is contained (§18.17.2.7).

dataTable (Table formula)

Data table. A *data table* is a range of cells that shows how changing certain values in one or more formulas affects the results of those formulas. A data table provides a shortcut for calculating multiple versions in one operation, and a way to include the results of all of the different variations in a sheet.

Both one- and two-input variable data tables can be created (see attribute dt2D).[*Example*: A one-input variable data table might be used to calculate how different interest rates affect a monthly mortgage payment, while a two-input variable data table might be used to calculate how different interest rates and loan terms will affect the mortgage payment. *end example*]

In a one-input variable data table, values are listed either down a column (column-oriented) or across a row (row-oriented) (see attribute dtr).

Formulas that are used in a one-input variable data table shall refer to an input cell (see attribute r1), the cell in which each input value from a data table is substituted. Any cell on a worksheet can be the input cell. Although the input cell does not need to be part of the data table, the formulas in data tables shall refer to that input cell.

Two-input variable data tables use only one formula with two lists of input values. The formula shall refer to two input cells (see attributes r1 and r2).

The top-left cell in the data table is called the *master cell*.

*[Guidance: It is recommended that Spreadsheet applications recalculate data tables whenever a worksheet is recalculated. end guidance]*

normal (Normal formula)

Normal cell formula (§18.17).

shared (Shared formula)

Shared formula. If a cell contains the same formula as another cell, the “shared” value can be used for the t attribute and the si attribute can be used to refer to the cell containing the formula. Two formulas are considered to be the same when their respective representations in R1C1-reference notation, are the same.

Parent Elements |
---|

c (§18.3.1.4); nc (§18.11.1.3); oc (§18.11.1.5) |

Attributes | Description |
---|---|

aca (Always Calculate Array) | Only applies to array formulas. [ The possible values for this attribute are defined by the W3C XML Schema |

bx (Assigns Value to Name) | Specifies that this formula assigns a value to a name. The possible values for this attribute are defined by the W3C XML Schema |

ca (Calculate Cell) | Indicates that this formula needs to be recalculated the next time calculation is performed. [ The possible values for this attribute are defined by the W3C XML Schema |

del1 (Input 1 Deleted) | Whether the first input cell for data table has been deleted. Applies to data table formula only. Written on master cell of data table formula only. The possible values for this attribute are defined by the W3C XML Schema |

del2 (Input 2 Deleted) | Whether the second input cell for data table has been deleted. Applies to data table formula only. Written on master cell of data table formula only. The possible values for this attribute are defined by the W3C XML Schema |

dt2D (Data Table 2-D) | Data table is two-dimentional. Only applies to the data tables function. Written on master cell of data table formula only. The possible values for this attribute are defined by the W3C XML Schema |

dtr (Data Table Row) |
The possible values for this attribute are defined by the W3C XML Schema |

r1 (Data Table Cell 1) | First input cell for data table. Only applies to the data tables array function "TABLE()". Written on master cell of data table formula only. The possible values for this attribute are defined by the |

r2 (Input Cell 2) | Second input cell for data table when dt2D is '1'. Only applies to the data tables array function "TABLE()".Written on master cell of data table formula only. The possible values for this attribute are defined by the |

ref (Range of Cells) | Range of cells which the formula applies to. Only required for shared formula, array formula or data table. Only written on the master formula, not subsequent formulas belonging to the same shared group, array, or data table. The possible values for this attribute are defined by the |

si (Shared Group Index) | Optional attribute to optimize load performance by sharing formulas. When a formula is a shared formula (t value is A cell is shared only when si is used and t is If a master cell of a shared formula range specifies that a particular cell is part of the shared formula range, and that particular cell does not use the si and t attributes to indicate that it is shared, then the particular cell's formula shall override the shared master formula. If this cell occurs in the middle of a range of shared formula cells, the earlier and later formulas shall continue sharing the master formula, and the cell in question shall not share the formula of the master cell formula. Loading and handling of a cell and formula using an si attribute and whose t value is Master cell references on the same sheet shall not overlap with each other. The possible values for this attribute are defined by the W3C XML Schema |

t (Formula Type) | Type of formula. The possible values for this attribute are defined by the |

[*Note*: The W3C XML Schema definition of this element’s content model (CT_CellFormula) is located in §A.2. *end note*]

© ISO/IEC29500: 2008.