org: References
3.5.1 References
----------------
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.
Field references
................
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(1) to use another, more general representation that looks
like this:
@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 (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(2) 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
Range references
................
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 one but last
@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 
Formula syntax for
Calc.
Field coordinates in formulas
.............................
For Calc formulas and Lisp formulas ‘@#’ and ‘$#’ can be used to get the
row or column number of the field where the formula result goes. The
traditional Lisp formula equivalents are ‘org-table-current-dline’ and
‘org-table-current-column’. Examples:
if(@# % 2, $#, string("")) column number on odd lines only
$3 = remote(FOO, @@#$2) copy column 2 from table FOO into
column 3 of the current table
For the second example, table FOO must have at least as many rows as the
current table. Note that this is inefficient(3) for large number of
rows.
Named references
................
‘$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 (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(4). 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.
Remote references
.................
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.
---------- Footnotes ----------
(1) 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’.
(2) 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.
(3) The computation time scales as O(N^2) because table FOO is parsed
for each field to be copied.
(4) ‘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.
Info Catalog
org: The spreadsheet
org: The spreadsheet
org: Formula syntax for Calc