WorgSheet Calc Intro

Table of Contents

• 1. Introduction
• 2. Getting acquainted with references
• 3. The mean row
• 4. Column and field formulas
• 5. The mean column
• 6. Interactively edit formulas
• 7. Calc and Elisp formulas
• 8. Debugging formulas
• 9. There’s more…

1 Introduction

This short tutorial will go through the basics that we need to know if we want to use org as a spreadsheet system. The original of this short story is available at worg. We start with Table 1,

Table 1: Initial Table

Student	Maths	Physics
Bertrand	13	9
Henri	15	14
Arnold	17	13

which is the output of this org mode code:

#+Name: tbl-1-wrgSprdC
#+Caption: Initial Table
#+Attr_Latex: :align lrr :font \footnotesize
#+Attr_Html: :cellpadding 3pt :rules groups :frame hsides
#+Attr_Html: :width 400 :align center
| Student  | Maths | Physics |
|----------+-------+---------|
| Bertrand |    13 |       9 |
| Henri    |    15 |      14 |
| Arnold   |    17 |      13 |

The purpose of this tutorial is to understand how to get from this simple Table 1 to Table 2, where we have the mean per student and per discipline.

Table 2: Desired result of Table 1

Student	Maths	Physics	Mean
Bertrand	13	9	11
Henri	15	14	14.5
Arnold	17	13	15
Means	15	12	13.5

2 Getting acquainted with references

Our first formula should result in the mean per student. We add a new column by hitting M-S-▷, i.e. Alt-Shift-CursorRight, and enter the column header.

| Student  | Maths | Physics | Mean      |
|----------+-------+---------+-----------|
| Bertrand |    13 |       9 | [Formula] |
| Henri    |    15 |      14 |           |
| Arnold   |    17 |      13 |           |

Before being able to insert a formula in place of [Formula], we need to know how to refer to a row, a column or a single field. To learn about references we type C-c ? while we are in a field. For example, if you are in the [Formula] field, C-c ? will tell you

line @2, col $4, ref @2$4 or D2

meaning that you are on the second row (or line) of the fourth column, and the reference for this field is either @2$4 or D2. From this information we can derive that the horizontal line doesn’t count – in this case. Beware, there are situations where these lines are counted; → see Section Environment of a Code Block in the org Manual, where indexing variable values is discussed.

At any moment, if you are lost in rows and columns, you can always turn on the reference visualization grid with C-c }:

Figure 1: Our Table, forced by C-c } to show its field names.

3 The mean row

Put the cursor in the (empty) [Formula] field. Now type :=vmean($2..$3) in this field; → see the Manual entry for (vmean..). This formula means: calculate the mean for fields from the second ($2) to the third ($3) field in this row. If you prefer to use the other notation, type :=vmean(B&..C&) – where the & character stands for “in this row”, which is implicit in the previous notation.

Figure 2: Enter the formula

While still in the row, we hit C-c C-c, [TAB], or [RET] – and we should observe two things:

1. the formula has been replaced by the result of the calculation and
2. a new line starting with #+TBLFM has been inserted at the bottom of the table.

| Student  | Maths | Physics | Mean |
|----------+-------+---------+------|
| Bertrand |    13 |       9 |   11 |
| Henri    |    15 |      14 |      |
| Arnold   |    17 |      13 |      |
#+TBLFM: @2$4=vmean($2..$3)

The #+TBLFM line will contain all the formulas for the table above, and we have to be very careful when editing it manually.

4 Column and field formulas

Ok, so now we have added one calculated field in our table. Manually we could proceed with three approaches. We could

• enter the same value :=vmean($2..$3) in every Mean field
• add another #+TBLFM: line for every field, like #+TBLFM: @3$4=vmean($2..$3) and #+TBLFM: @4$4=vmean($2..$3), and evaluate it with C-c C-c
• add #::@3$4=vmean($2..$3)::@4$4=vmean($2..$3) to the existing #+TBLFM: line and evaluate them with C-c C-c

But the spreadsheet formalism also provides a way to compute the formulas for all fields in the column. So, it offers both a column formula and a field formula syntax.

To replace the field formula with a column formula, we go back to the field where it has been defined and type =vmean($2..$3). Looks similar. We can see that the only difference with what we’ve inserted previously is that the formula is prefixed by a plain = instead of :=. So, to insert a column formula you have to dismiss the colon: column? → no colon. When we’re done, we hit C-c C-c – or [TAB], or [RET] – in the field: we might be prompted to decide whether we want to replace the formula with a column formula. But that depends – probably on the version of org mode.

After confirmation the value in the field should be the same as before, i.e., 11, and we can now update all the fields in this column by reapplying all formulas with C-u C-c * with point in the table or C-c C-c with point in the #+TBLFM line. Anyway, the result is

| Student  | Maths | Physics | Mean |
|----------+-------+---------+------|
| Bertrand |    13 |       9 |   11 |
| Henri    |    15 |      14 | 14.5 |
| Arnold   |    17 |      13 |   15 |
#+TBLFM: $4=vmean($2..$3)

As our single formula in #+TBLFM: now applies to the entire column, it doesn’t contain any reference to a row. The formula was previously applied for the @2$4 field, and it is now applied for the $4 column.

5 The mean column

Finally, we can add the row for the means per discipline. This row contains two field formulas, each one calculating the mean for the fields above in the same column:

| Student  | Maths | Physics | Mean |
|----------+-------+---------+------|
| Bertrand |    13 |       9 |   11 |
| Henri    |    15 |      14 | 14.5 |
| Arnold   |    17 |      13 |   15 |
|----------+-------+---------+------|
| Means    |    15 |      12 |      |
#+TBLFM: $4=vmean($2..$3)::@5$2=vmean(@2$2..@4$2)::@5$3=vmean(@2$3..@4$3)

Which brings up this table:

Table 3: Desired result of Table 1 accomplished.

Student	Maths	Physics	Mean
Bertrand	13	9	11
Henri	15	14	14.5
Arnold	17	13	15
Means	15	12	13.5

We wonder if adding ::@5=vmean(@2..@4) also works.

| Student                         | Maths | Physics | Mean |
|---------------------------------+-------+---------+------|
| Bertrand                        |    13 |       9 |   11 |
| Henri                           |    15 |      14 | 14.5 |
| Arnold                          |    17 |      13 |   15 |
|---------------------------------+-------+---------+------|
| (Bertrand + Henri + Arnold) / 3 |    15 |      12 | 13.5 |
#+TBLFM: $4=vmean($2..$3)::@5=vmean(@2..@4)

Kind of. So, intuitively we might try with @5$2..@5$4=vmean(@2..@4)

| Student  | Maths | Physics | Mean |
|----------+-------+---------+------|
| Bertrand |    13 |       9 | 11.0 |
| Henri    |    15 |      14 | 14.5 |
| Arnold   |    17 |      13 | 15.0 |
|----------+-------+---------+------|
|          |    15 |      12 | 13.5 |
#+TBLFM: $4=vmean($2..$3);%.1f::@5$2..@5$3=vmean(@2..@4)

Nice. Well, of course the means only look good, when aligned left. So what we really wrote in the org mode script to get Table 3 was

#+Name: tbl-3-wrgSprdC
#+Caption: Desired result of Table 1 accomplished.
#+Attr_Latex: :align lrrS[table-format=3.2] :font \footnotesize
#+Attr_Html: :cellpadding 3pt :rules groups :frame hsides
#+Attr_Html: :width 400 :align center
|   | Student  | Maths | Physics | Mean |
|---+----------+-------+---------+------|
| / |          |  <c>  |   <c>   | <l>  |
|   | Bertrand |  13   |    9    | 11   |
|   | Henri    |  15   |   14    | 14.5 |
|   | Arnold   |  17   |   13    | 15   |
|---+----------+-------+---------+------|
|   | Means    |  15   |   12    | 13.5 |
#+TBLFM: $5=vmean($3..$4)::@6$3=vmean(@3$3..@5$3)::@6$4=vmean(@3$4..@5$4)

And it would also look good if we had something like digital marker alignment. According to Subsection Formula syntax for Calc of Section The Spreadsheet in the org mode Manual’s Table Chapter we can write 11.0 and 15.0 instead of 11 and 15 by appending ;%.1f to $4=vmean($2..$3). For our latex output we use tabular formatting digit S provided by siunitx; → see Aligning numbers by decimal points in table columns stackexchange entry. It might be neccessary to read the initial table and process it to just another output table which fills our needs. Or we might get an idea from the short trip to elisp in Section Calc and Elisp formulas after …

6 Interactively edit formulas

For now we have been defining formulas by inserting them directly in the table cells: typing = in a field starts the definition for a column formula and typing := starts a definition for a field formula.

We can edit formulas in the minibuffer: use C-c = for editing column formulas or C-u C-c = for field formulas.

But we can also edit formulas more interactively in a dedicated buffer by typing C-c '. This new buffer lists all the formulas for the table at point and provides facilities to edit the references.

When the cursor is above a reference, the corresponding field in the table get highlighted. Awesome! But we can do more than that: we can actually select the reference by using shift with the cursor keys.

Figure 3: “Where am I?” in org mode‘ish.

We might worry that moving a table’s column with M-◁ or M-▷ or a table’s row with M-△ or M-▽ might confuse the references in the #+TBLFM: line, but each move automagically updates the references in this line.

7 Calc and Elisp formulas

We conclude our short trip to the default org mode table formulas in calc manner with a look into the elisp inclusion.

Our example objective is to associate each student’s mean score with a decimal of the number π.

For this we need to tell org about our version of π. We can do this by adding the #+CONSTANTS: line of

#+CONSTANTS: pi=3.14159265358979323846
positions       0123456789012345657890

in the document header. Here we additionally noted the string indizes we want to access below. To initialize the value, i.e., make it known to org, we hit C-c C-c on the #+CONSTANTS line. This constant can be accessed as $pi in elisp and calc formulas.

Then we assign the emacs lisp formula

'(substring (number-to-string $pi) (round $4) (1+ (round $4)))

to the fifth column, using $5=elisp-string;N. The elisp parts of this assignment are

• (substring..) with three arguments S A B – get a substring of string S beginning at string position A and ending at, not including, position B. So the first two values, indexed 0 and 1, would be “3” and “.”
• number-to-string $pi – convert the constant pi into a string
• (round..) – get the integer part of the value in column $4. The fact that rounding 14.5 delivers 14 need some explanation. Usually we expect 14.5 to be rounded to 15.

We’ve already seen the $5= part for introducing a column formula assignment. And ;N is a mode string which considers the values of fields to be numeric values, not strings. We mentioned this construction above when formatting the table entry of student’s mean with %.1f. This affects the automatic alignment in the table. Numbers are aligned right, strings left. But that’s only half of the story, because without this mode string the output is #ERROR. For our purposes we are happy with two statements from the manual, beginning with the description of the mode string ;N with the task to

“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’. […]

With Emacs Lisp forms, you need to be conscious about the way field references are interpolated into the form. By default, a reference is interpolated as a Lisp string (in double-quotes) containing the field.”

— Subsections Formula syntax for Calc and Formula syntax for Lisp of Section The Spreadsheet in the org mode Manual’s Table Chapter

If the mean of a student is 10, this formula returns the 9^th decimal of π.

Table 4: elisp encoded mean values of Table 3.

Student	Maths	Physics	Mean	π
Bertrand	13	09	11.0	5
Henri	15	14	14.5	7
Arnold	17	13	15.0	9

8 Debugging formulas

If you come back to Table 4 but feel a bit lazy trying to understand what the emacs lisp function does, you might as well want to debug the formula and follow the computation step by step.

Turn the formulas debugger on with C-c { and hit C-c C-c in a field or C-u C-c * anywhere on this table. This will perform the computations of the formulas one by one, and display details about the steps of the computation for each formula in a separate buffer.

With π shortened to 3.1415.., the fourth buffer shows

Substitution history of formula
Orig:   '(substring (number-to-string $pi) (round $4) (1+ (round $4)));N
$xyz->  '(substring (number-to-string 3.1415..) (round $4) (1+ (round $4)))
@r$c->  '(substring (number-to-string 3.1415..) (round $4) (1+ (round $4)))
$1->    '(substring (number-to-string 3.1415..) (round 14.5) (1+ (round 14.5)))
Result: 7
Format: NONE
Final:  7

Once you’re done checking the formulas, you can switch the debugger off by hitting C-c { again.

9 There’s more…

Using org tables as a spreadsheet system turns out to be interesting. But we can do a lot more. We can use relative references, define names for columns and parameters for formulas, define fields that should be automatically recalculated.

Two hints for proceeding

• Try another short tutorial on emacs lisp in table formulas.
• Have a look at the Section Advanced Features in the Table Chapter of the org mode Manual.

Files: wrgsprdclc.pdf, sorry the org file is “not allowed for security reasons”.

Author: Worg people and pjs64

Created: 2020-12-02 Wed 09:07