Next: Formula syntax for Calc, Up: The Spreadsheet [Contents][Index]

To compute fields in the table from other fields, formulas must
reference other fields or ranges. In Org, fields can be referenced by
name, by absolute coordinates, and by relative coordinates. To find
out what the coordinates of a field are, press `C-c ?` in
that field, or press `C-c }` to toggle the display of a grid.

Formulas can reference the value of another field in two ways. Like
in any other spreadsheet, you may reference fields with
a letter/number combination like ‘`B3`’, meaning the second field in the
third row. However, Org prefers to use another, more general
representation that looks like this:^{20}

@ROW$COLUMN

Column specifications can be absolute like ‘`$1`’, ‘`$2`’, …, ‘`$N`’, or
relative to the current column, i.e., the column of the field which is
being computed, like ‘`$+1`’ or ‘`$-2`’. ‘`$<`’ and ‘`$>`’ are immutable
references to the first and last column, respectively, and you can use
‘`$>>>`’ to indicate the third column from the right.

The row specification only counts data lines and ignores horizontal
separator lines, or “hlines”. Like with columns, you can use absolute
row numbers ‘`@1`’, ‘`@2`’, …, ‘`@N`’, and row numbers relative to the
current row like ‘`@+3`’ or ‘`@-1`’. ‘`@<`’ and ‘`@>`’ are immutable
references the first and last row in the table, respectively. You may
also specify the row relative to one of the hlines: ‘`@I`’ refers to the
first hline, ‘`@II`’ to the second, etc. ‘`@-I`’ refers to the first such
line above the current line, ‘`@+I`’ to the first such line below the
current line. You can also write ‘`@III+2`’ which is the second data
line after the third hline in the table.

‘`@0`’ and ‘`$0`’ refer to the current row and column, respectively, i.e.,
to the row/column for the field being computed. Also, if you omit
either the column or the row part of the reference, the current
row/column is implied.

Org’s references with *unsigned* numbers are fixed references in the
sense that if you use the same reference in the formula for two
different fields, the same field is referenced each time. Org’s
references with *signed* numbers are floating references because the
same reference operator can reference different fields depending on
the field being calculated by the formula.

Here are a few examples:

‘@2$3’ | 2nd row, 3rd column (same as ‘C2’) |

‘$5’ | column 5 in the current row (same as ‘E&’) |

‘@2’ | current column, row 2 |

‘@-1$-3’ | field one row up, three columns to the left |

‘@-I$2’ | field just under hline above current row, column 2 |

‘@>$5’ | field in the last row, in column 5 |

You may reference a rectangular range of fields by specifying two
field references connected by two dots ‘`..`’. If both fields are in
the current row, you may simply use ‘`$2..$7`’, but if at least one
field is in a different row, you need to use the general ‘`@ROW$COLUMN`’
format at least for the first field, i.e., the reference must start
with ‘`@`’ in order to be interpreted correctly. Examples:

‘$1..$3’ | first three fields in the current row |

‘$P..$Q’ | range, using column names (see Advanced features) |

‘$<<<..$>>’ | start in third column, continue to the last but one |

‘@2$1..@4$3’ | six fields between these two fields (same as ‘A2..C4’) |

‘@-1$-2..@-1’ | 3 fields in the row above, starting from 2 columns on the left |

‘@I..II’ | between first and second hline, short for ‘@I..@II’ |

Range references return a vector of values that can be fed into Calc
vector functions. Empty fields in ranges are normally suppressed, so
that the vector contains only the non-empty fields. For other options
with the mode switches ‘`E`’, ‘`N`’ and examples, see Formula syntax for Calc.

One of the very first actions during evaluation of Calc formulas and
Lisp formulas is to substitute ‘`@#`’ and ‘`$#`’ in the formula with the
row or column number of the field where the current result will go to.
The traditional Lisp formula equivalents are `org-table-current-dline`

and `org-table-current-column`

. Examples:

- ‘
`if(@# % 2, $#, string(""))`’ Insert column number on odd rows, set field to empty on even rows.

- ‘
`$2 = '(identity remote(FOO, @@#$1))`’ Copy text or values of each row of column 1 of the table named

`FOO`into column 2 of the current table.- ‘
`@3 = 2 * remote(FOO, @@1$$#)`’ Insert the doubled value of each column of row 1 of the table named

`FOO`into row 3 of the current table.

For the second and third examples, table `FOO` must have at
least as many rows or columns as the current table. Note that this is
inefficient^{21} for large number of rows.

‘`$name`’ is interpreted as the name of a column, parameter or constant.
Constants are defined globally through the variable
`org-table-formula-constants`

, and locally—for the file—through
a line like this example:

#+CONSTANTS: c=299792458. pi=3.14 eps=2.4e-6

Also, properties (see Properties and Columns) can be used as
constants in table formulas: for a property ‘`Xyz`’ use the name
‘`$PROP_Xyz`’, and the property will be searched in the current outline
entry and in the hierarchy above it. If you have the ‘`constants.el`’
package, it will also be used to resolve constants, including natural
constants like ‘`$h`’ for Planck’s constant, and units like ‘`$km`’ for
kilometers^{22}. Column names and parameters can be specified in
special table lines. These are described below, see Advanced features. All names must start with a letter, and further consist
of letters and numbers.

You may also reference constants, fields and ranges from a different table, either in the current file or even in a different file. The syntax is

remote(NAME,REF)

where `NAME` can be the name of a table in the current file
as set by a ‘`#+NAME:`’ line before the table. It can also be the ID of
an entry, even in a different file, and the reference then refers to
the first table in that entry. `REF` is an absolute field or
range reference as described above for example ‘`@3$3`’ or ‘`$somename`’,
valid in the referenced table.

When `NAME` has the format ‘`@ROW$COLUMN`’, it is substituted
with the name or ID found in this field of the current table. For
example ‘`remote($1, @@>$2)`’ ⇒ ‘`remote(year_2013, @@>$1)`’. The format
‘`B3`’ is not supported because it can not be distinguished from a plain
table name or ID.

Org understands references typed by the user as ‘`B4`’, but it
does not use this syntax when offering a formula for editing. You can
customize this behavior using the variable
`org-table-use-standard-references`

.

The computation time scales as O(N^2) because table
`FOO` is parsed for each field to be copied.

The file ‘`constants.el`’ can supply the values of constants in
two different unit systems, ‘`SI`’ and ‘`cgs`’. Which one is used depends
on the value of the variable `constants-unit-system`

. You can use the
‘`STARTUP`’ options ‘`constSI`’ and ‘`constcgs`’ to set this value for the
current buffer.

Next: Formula syntax for Calc, Up: The Spreadsheet [Contents][Index]