Chapter 3: Tables
The row speciﬁcation 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 ﬁrst and last
row in the table, respectively. You may also specify the row relative to
one of the hlines: @I refers to the ﬁrst hline, @II to the second, etc. @-I refers to the ﬁrst
such line above the current line, @+I to the ﬁrst 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 ﬁeld 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 ﬁxed references in the sense that if you use
the same reference in the formula for two diﬀerent ﬁelds, the same ﬁeld will be referenced
each time. Org’s references with signed numbers are ﬂoating references because the same
reference operator can reference diﬀerent ﬁelds depending on the ﬁeld being calculated by
Here are a few examples:
2nd row, 3rd column (same as C2)
column 5 in the current row (same as E&)
current column, row 2
the ﬁeld one row up, three columns to the left
ﬁeld just under hline above current row, column 2
ﬁeld in the last row, in column 5
You may reference a rectangular range of ﬁelds by specifying two ﬁeld references connected
by two dots ‘..’. If both ﬁelds are in the current row, you may simply use ‘$2..$7’, but
if at least one ﬁeld is in a diﬀerent row, you need to use the general @row$column format
at least for the ﬁrst ﬁeld (i.e the reference must start with ‘@’ in order to be interpreted
ﬁrst three ﬁelds in the current row
range, using column names (see under Advanced)
start in third column, continue to the last but one
6ﬁelds between these two ﬁelds (same as A2..C4)
3ﬁelds in the row above, starting from 2 columns on the left
between ﬁrst and second hline, short for @I..@II
Range references return a vector of values that can be fedinto Calc vector functions. Empty
ﬁelds in ranges are normally suppressed, so that the vector contains only the non-empty
ﬁelds. For other options with the mode switches ‘E’, ‘N’ and examples see Section3.5.2
[Formula syntax for Calc], page 27.
For backward compatibility you can also use special names like $LR5 and $LR12 to refer in a stable way
to the 5th and 12th ﬁeld in the last row of the table. However, this syntax is deprecated, it should not
be used for new documents. Use @>$ instead.
Chapter 3: Tables
Field coordinates in formulas
One of the very ﬁrst actions during evaluation of Calc formulas and Lisp formulas is to
substitute @# and $# in the formula with the row or column number of the ﬁeld 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 ﬁeld 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
2of 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 ineﬃcient
for large number of
‘$name’ is interpreted as the name of a column, parameter or constant. Constants are
deﬁned globally through the option org-table-formula-constants, and locally (for the
ﬁle) through a line like
#+CONSTANTS: c=299792458. pi=3.14 eps=2.4e-6
Also properties (seeChapter7[Propertiesandcolumns],page64) 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
names and parameters can be speciﬁed in special table lines. These are described below,
seeSection3.5.10[Advancedfeatures],page34. All names must start with a letter, and
further consist of letters and numbers.
You may also reference constants, ﬁelds and ranges from a diﬀerent table, either in the
current ﬁle or even in a diﬀerent ﬁle. The syntax is
where NAME can be the name of a table in the current ﬁle as set by a #+NAME: Name line
before the table. It can also be the ID of an entry, even in a diﬀerent ﬁle, and the reference
then refers to the ﬁrst table in that entry. REF is an absolute ﬁeld or range reference as
described above for example @3$3 or $somename, valid in the referenced table.
The computation time scales as O(N^2) because the table named FOO is parsed for each ﬁeld to be read.
constants.el can supply the values of constants in two diﬀerent 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 buﬀer.
Chapter 3: Tables
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 ﬁeld 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.
3.5.2 Formula syntax for Calc
Aformula can be any algebraic expression understood by the Emacs Calc package. Note
that calc has the non-standard convention that ‘/’ has lower precedence than ‘*’, so that
‘a/b*c’ is interpreted as ‘a/(b*c)’. Before evaluation by calc-eval (seeSection“Calling
Calc from Your Lisp Programs” in GNU Emacs Calc Manual),variablesubstitutiontakes
place according to the rules described above. The range vectors can be directly fed into the
Calc vector functions like ‘vmean’ and ‘vsum’.
Aformula can contain an optional mode string after a semicolon. This string consists
of ﬂags to inﬂuence Calc and other modes during execution. By default, Org uses the
standard Calc modes (precision 12, angular units degrees, fraction and symbolic modes
oﬀ). The display format, however, has been changed to (float 8) to keep tables compact.
The default settings can be conﬁgured using the option org-calc-default-modes.
List of modes:
Set the internal Calc calculation precision to 20 digits.
n3, s3, e2, f4
Normal, scientiﬁc, engineering or ﬁxed format of the result of Calc passed back
to Org. Calc formatting is unlimited in precision as long as the Calc calculation
precision is greater.
Degree and radian angle modes of Calc.
Fraction and symbolic modes of Calc.
Duration computations in Calc or Lisp, seeSection3.5.4[Durationsandtime
values], page 29.
If and how to consider empty ﬁelds. Without ‘E’ empty ﬁelds in range references
are suppressed so that the Calc vector or Lisp list contains only the non-empty
ﬁelds. With ‘E’ the empty ﬁelds are kept. For empty ﬁelds in ranges or empty
ﬁeld references the value ‘nan’ (not a number) is used in Calc formulas and
the empty string is used for Lisp formulas. Add ‘N’ to use 0 instead for both
formula types. For the value of a ﬁeld the mode ‘N’ has higher precedence than
Interpret all ﬁelds as numbers, use 0 for non-numbers. See the next section to
see how this is essential for computations with Lisp formulas. In Calc formulas
it is used only occasionally because there number strings are already interpreted
as numbers without ‘N’.
Literal, for Lisp formulas only. See the next section.
Unless you use large integer numbers or high-precision-calculation and -display for ﬂoating
point numbers you may alternatively provide a ‘printf’ format speciﬁer to reformat the
Chapter 3: Tables
Calc result after it has been passed back to Org instead of letting Calc already do the
.A few examples:
Sum of ﬁrst and second ﬁeld
Same, format result to two decimals
Math functions can be used
Reformat current cell to 1 decimal
Degrees F -> C conversion
Hz -> cm conversion, using constants.el
Compute in degrees, precision 3, display SCI 1
Same, but use printf speciﬁer for display
Taylor series of $3, at x=7, second degree
Calc also contains a complete set of logical operations, (seeSection“LogicalOperations”
in GNU Emacs Calc Manual). Forexample
if($1 < 20, teen, string(""))
"teen" if age $1 is less than 20, else the Org table result ﬁeld is set to empty
with the empty string.
if("$1" == "nan" || "$2" == "nan", string(""), $1 + $2); E f-1
Sum of the ﬁrst two columns. When at least one of the input ﬁelds is empty
the Org table result ﬁeld is set to empty. ‘E’ is required to not convert empty
ﬁelds to 0. ‘f-1’ is an optional Calc format string similar to ‘%.1f’ but leaves
empty results empty.
if(typeof(vmean($1..$7)) == 12, string(""), vmean($1..$7); E
Mean value of a range unless there is any empty ﬁeld. Every ﬁeld in the range
that is empty is replaced by ‘nan’ which lets ‘vmean’ result in ‘nan’. Then
‘typeof == 12’ detects the ‘nan’ from ‘vmean’ and the Org table result ﬁeld is
set to empty. Use this when the sample set is expected to never have missing
if("$1..$7" == "", string(""), vmean($1..$7))
Mean value of a range with empty ﬁelds skipped. Every ﬁeld in the range that
is empty is skipped. When all ﬁelds in the range are empty the mean value is
not deﬁned and the Org table result ﬁeld is set to empty. Use this when the
sample set can have a variable size.
To complete the example before: Mean value of a range with empty ﬁelds
counting as samples with value 0. Use this only when incomplete sample sets
should be padded with 0 to the full size.
You can add your own Calc functions deﬁned in Emacs Lisp with defmath and use them
in formula syntax for Calc.
The ‘printf’ reformatting is limited in precision because the value passed to it is converted into an
‘integer’ or ‘double’. The ‘integer’ is limited in size by truncating the signed value to 32 bits. The
‘double’ is limited in precision to 64bits overall which leaves approximately 16 signiﬁcant decimal digits.
Chapter 3: Tables
3.5.3 Emacs Lisp forms as formulas
It is also possible to write a formula in Emacs Lisp. This can be useful for string manipu-
lation and control structures, if Calc’s functionality is not enough.
If a formula starts with an apostrophe followed by an opening parenthesis, then it is
evaluated as a Lisp form. The evaluation should return either a string or a number. Just
as with calc formulas, you can specify modes and a printf format after a semicolon.
With Emacs Lisp forms, you need to be conscious about the way ﬁeld references are
interpolated into the form. By default, a reference will be interpolated as a Lisp string
(in double-quotes) containing the ﬁeld. If you provide the ‘N’ mode switch, all referenced
elements will be numbers (non-number ﬁelds will be zero) and interpolated as Lisp numbers,
without quotes. If you provide the ‘L’ ﬂag, all ﬁelds will be interpolated literally, without
quotes. I.e.,ifyou want a reference to be interpreted as a string by the Lisp form, enclose the
reference operator itself in double-quotes, like "$3". Ranges are inserted as space-separated
ﬁelds, so you can embed them in list or vector syntax.
Here are a few examples—note how the ‘N’ mode is used when we do computations in
'(concat (substring $1 1 2) (substring $1 0 1) (substring $1 2))
Swap the ﬁrst two characters of the content of column 1.
'(+ $1 $2);N
Add columns 1 and 2, equivalent to Calc’s $1+$2.
'(apply '+ '($1..$4));N
Compute the sum of columns 1 to 4, like Calc’s vsum($1..$4).
3.5.4 Durations and time values
If you want to compute time values use the T ﬂag, either in Calc formulas or Elisp formulas:
| Task 1 |
Task 2 |
1:47 | 03:59:00 |
| 3:02:20 | -2:07:00 |
Input duration values must be of the form HH:MM[:SS], where seconds are optional.
With the T ﬂag, computed durations will be displayed as HH:MM:SS (see the ﬁrst formula
above). With the t ﬂag, computed durations will be displayed according to the value of the
option org-table-duration-custom-format, which defaults to 'hours and will display
the result as a fraction of hours (see the second formula in the example above).
Negative duration values can be manipulated as well, and integers will be considered as
seconds in addition and subtraction.
3.5.5 Field and range formulas
To assign a formula to a particular ﬁeld, type it directly into the ﬁeld, preceded by ‘:=’,
for example ‘:=vsum(@II..III)’. When you press TAB or RET or C-c C-c with the cursor
still in the ﬁeld, the formula will be stored as the formula for this ﬁeld, evaluated, and the
current ﬁeld will be replaced with the result.
Documents you may be interested
Documents you may be interested