## Table of Contents

## 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,

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.

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 }`

:

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

While still in the row, we hit `C-c C-c`

, `[TAB]`

, or `[RET]`

– and we should
observe two things:

- the formula has been replaced by the result of the calculation and
- 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:

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.

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

— SubsectionsFormula syntax for CalcandFormula syntax for Lispof SectionThe Spreadsheetin the org mode Manual’sTableChapter

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

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

Created: 2020-12-02 Wed 09:07

## 1 Comment