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 2nd field in the 3rd row.
However, Org prefers^{22} to use another, more general
representation that looks like this:

@row$column

Column specifications can be absolute like `$1`

,
`$2`

,...`$`

, or relative to the current column (i.e., the
column of the field which is being computed) like `N``$+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 (hlines). Like with columns, you can use absolute row numbers
`@1`

, `@2`

,...`@`

, and row numbers relative to the
current row like `N``@+3`

or `@-1`

. `@<`

and `@>`

are
immutable references the first and last^{23} 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 will be 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 the 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 under Advanced) $<<<..$>> start in third column, continue to the last but one @2$1..@4$3 6 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/third example, the table named `FOO`

must have
at least as many rows/columns as the current table. Note that this is
inefficient^{24} for large number of
rows/columns.

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

, and locally (for the file) through a
line like

#+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^{25}. 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-OR-ID,REF)

where NAME can be the name of a table in the current file as set by a
`#+NAME: 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.

Indirection of NAME-OR-ID: When NAME-OR-ID has the format `@ROW$COLUMN`

it will be 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 will understand references typed by the
user as ‘`B4`’, but it will not use this syntax when offering a formula
for editing. You can customize this behavior using the option
`org-table-use-standard-references`

.

For backward compatibility
you can also use special names like `$LR5`

and `$LR12`

to refer in
a stable way to the 5th and 12th field in the last row of the table.
However, this syntax is deprecated, it should not be used for new documents.
Use `@>$`

instead.

The computation time scales as O(N^2) because the table
named `FOO`

is parsed for each field to be read.

`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]