3.5.2 Formula syntax for Calc

A formula 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 (see Calling Calc from Your Lisp Programs in GNU Emacs Calc Manual), variable substitution takes place according to the
rules described above.
The range vectors can be directly fed into the Calc vector functions
like ‘vmean’ and ‘vsum’.

A formula can contain an optional mode string after a semicolon. This
string consists of flags to influence Calc and other modes during
execution. By default, Org uses the standard Calc modes (precision
12, angular units degrees, fraction and symbolic modes off). The display
format, however, has been changed to (float 8) to keep tables
compact. The default settings can be configured using the option
org-calc-default-modes.

List of modes:

p20

Set the internal Calc calculation precision to 20 digits.

n3, s3, e2, f4

Normal, scientific, engineering or fixed 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.

If and how to consider empty fields. Without ‘E’ empty fields in range
references are suppressed so that the Calc vector or Lisp list contains only
the non-empty fields. With ‘E’ the empty fields are kept. For empty
fields in ranges or empty field 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 field the mode ‘N’ has higher precedence than ‘E’.

N

Interpret all fields 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’.

L

Literal, for Lisp formulas only. See the next section.

Unless you use large integer numbers or high-precision-calculation and
-display for floating point numbers you may alternatively provide a
‘printf’ format specifier to reformat the Calc result after it has been
passed back to Org instead of letting Calc already do the
formatting33. A
few examples:

$1+$2 Sum of first and second field
$1+$2;%.2f Same, format result to two decimals
exp($2)+exp($1) Math functions can be used
$0;%.1f Reformat current cell to 1 decimal
($3-32)*5/9 Degrees F -> C conversion
$c/$1/$cm Hz -> cm conversion, using constants.el
tan($1);Dp3s1 Compute in degrees, precision 3, display SCI 1
sin($1);Dp3%.1e Same, but use printf specifier for display
taylor($3,x=7,2) Taylor series of $3, at x=7, second degree

Calc also contains a complete set of logical operations, (see Logical Operations in GNU Emacs Calc Manual). For example

if($1 < 20, teen, string(""))

"teen" if age $1 is less than 20, else the Org table result field is set to
empty with the empty string.

if("$1" == "nan" || "$2" == "nan", string(""), $1 + $2); E f-1

Sum of the first two columns. When at least one of the input fields is empty
the Org table result field is set to empty. ‘E’ is required to not
convert empty fields 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 field. Every field 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 field is set to empty. Use this when
the sample set is expected to never have missing values.

if("$1..$7" == "[]", string(""), vmean($1..$7))

Mean value of a range with empty fields skipped. Every field in the range
that is empty is skipped. When all fields in the range are empty the mean
value is not defined and the Org table result field is set to empty. Use
this when the sample set can have a variable size.

vmean($1..$7); EN

To complete the example before: Mean value of a range with empty fields
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 defined in Emacs Lisp with defmath
and use them in formula syntax for Calc.

Footnotes

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 64
bits overall which leaves approximately 16 significant decimal digits.