Next: Emacs Lisp forms as formulas, Previous: References, Up: The Spreadsheet [Contents][Index]
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)Calling Calc from
Your Lisp 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
variable org-calc-default-modes.
Set the internal Calc calculation precision to 20 digits.
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.
Degree and radian angle modes of Calc.
Fraction and symbolic modes of Calc.
Units simplification mode of Calc. Calc is also a symbolic calculator and is capable of working with values having a unit, represented with numerals followed by a unit string in Org table cells. This mode instructs Calc to simplify the units in the computed expression before returning the result.
Duration computations in Calc or Lisp, Durations and time values.
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’.
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’.
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
formatting21. 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 |
| ‘vmean($2..$7)’ | Compute column range mean, using vector function |
| ‘vmean($2..$7);EN’ | Same, but treat empty fields as 0 |
| ‘taylor($3,x=7,2)’ | Taylor series of $3, at x=7, second degree |
Calc also contains a complete set of logical operations (see (calc)Logical Operations). For example
‘"teen"’ if age ‘$1’ is less than 20, else the Org table result field is set to empty with the empty string.
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.
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.
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.
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.
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.
Next: Emacs Lisp forms as formulas, Previous: References, Up: The Spreadsheet [Contents][Index]