[Table of Contents] [docx version]
SpreadsheetML Reference Material - Table of Contents
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 have type ST_CellFormulaType, and are as follows:
• array (Array Entered)
• dataTable (Table Formula, see below)
• normal (Normal)
• shared (Shared Formula)
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 view and compare the results of all of the different variations together on a worksheet.
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 see how different interest rates affect a monthly mortgage payment, while a two-input variable data table might be used to show how different interest rates and loan terms will affect the mortgage payment. end example]
Data tables shall be recalculated whenever a worksheet is recalculated.
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.
|
Parent Elements |
|
Attributes |
Description |
|
aca (Always Calculate Array) |
true indicates that this formula is an array formula and the entire array shall be calculated in full. If false the individual cells of the array shall be calculated as needed.
[Note: The primary case where an array formula shall be calcuated in part instead of in full is when some cells in the array depend on other cells that are semi-calculated, e.g., contains the function =RAND(). end note]
The possible values for this attribute are defined by the XML Schema boolean datatype. |
|
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 XML Schema boolean datatype. |
|
ca (Calculate Cell) |
Indicates that this formula needs to be recalculated the next time calculation is performed. For example, this is always set on volatile functions, like =RAND(), and circular references.
The possible values for this attribute are defined by the XML Schema boolean datatype. |
|
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 XML Schema boolean datatype. |
|
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 XML Schema boolean datatype. |
|
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 XML Schema boolean datatype. |
|
dtr (Data Table Row) |
true if one-dimentional data table is a row, otherwise it's a column. 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 XML Schema boolean datatype. |
|
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 ST_CellRef simple type (§3.18.8). |
|
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 ST_CellRef simple type (§3.18.8). |
|
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 formula's belonging to the same shared group, array, or data table.
The possible values for this attribute are defined by the ST_Ref simple type (§3.18.64). |
|
si (Shared Group Index) |
Optional attribute to optimize load performance by sharing formulas.
When a formula is a shared formula (t value is shared) then this value indicates the group to which this particular cell's formula belongs. The first formula in a group of shared formulas is saved in the f element. This is considered the 'master' formula cell. Subsequent cells sharing this formula need not have the formula written in their f element. Instead, the attribute si value for a particular cell is used to figure what the formula expression should be based on the cell's relative location to the master formula cell.
A cell is shared only when si is used and t is shared. The formula expression for a cell that is specified to be part of a shared formula (and is not the master) shall be ignored, and the master formula shall override.
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 shared, located outside the range specified in the master cell associated with the si group, is implementation defined.
Master cell references on the same sheet shall not overlap with each other.
The possible values for this attribute are defined by the XML Schema unsignedInt datatype. |
|
t (Formula Type) |
Type of formula.
The possible values for this attribute are defined by the ST_CellFormulaType simple type (§3.18.7). |
The following XML Schema fragment defines the contents of this element:
<complexType name="CT_CellFormula">
<simpleContent>
<extension base="ST_Formula">
<attribute name="t" type="ST_CellFormulaType" use="optional" default="normal"/>
<attribute name="aca" type="xsd:boolean" use="optional" default="false"/>
<attribute name="ref" type="ST_Ref" use="optional"/>
<attribute name="dt2D" type="xsd:boolean" use="optional" default="false"/>
<attribute name="dtr" type="xsd:boolean" use="optional" default="false"/>
<attribute name="del1" type="xsd:boolean" use="optional" default="false"/>
<attribute name="del2" type="xsd:boolean" use="optional" default="false"/>
<attribute name="r1" type="ST_CellRef" use="optional"/>
<attribute name="r2" type="ST_CellRef" use="optional"/>
<attribute name="ca" type="xsd:boolean" use="optional" default="false"/>
<attribute name="si" type="xsd:unsignedInt" use="optional"/>
<attribute name="bx" type="xsd:boolean" use="optional" default="false"/>
</extension>
</simpleContent>
</complexType>