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 calc-eval), 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.

D, R

Degree and radian angle modes of Calc.

F, S

Fraction and symbolic modes of Calc.

T, t

Duration computations in Calc or Lisp, see Durations and time values.

E

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 formatting¹. 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). 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.