Next: , Previous: , Up: The Spreadsheet   [Contents][Index]


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

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.

u

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.

T’, ‘t’, ‘U

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 formatting21. A few examples:

$1+$2Sum of first and second field
$1+$2;%.2fSame, format result to two decimals
exp($2)+exp($1)Math functions can be used
$0;%.1fReformat current cell to 1 decimal
($3-32)*5/9Degrees F → C conversion
$c/$1/$cmHz → cm conversion, using ‘constants.el
tan($1);Dp3s1Compute in degrees, precision 3, display SCI 1
sin($1);Dp3%.1eSame, but use printf specifier for display
vmean($2..$7)Compute column range mean, using vector function
vmean($2..$7);ENSame, 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

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

(21)

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: , Previous: , Up: The Spreadsheet   [Contents][Index]