org: Formula syntax for Calc
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’ (
calc-eval (calc)Calling Calc
from Your Programs.), 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, 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(1).
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, (
Logical Operations (calc)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.
---------- Footnotes ----------
(1) 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.
Info Catalog
org: References
org: The spreadsheet
org: Formula syntax for Lisp