How can I fill a column with relative indexing in org-mode?

Question

It turns out that ses-mode (Simple Emacs Spreadsheet) is not yet ready for doing operations on rows/columns.

And so I'm turning my attention to org-mode.

I store the following data in a .org file

| Name              | Height | Weight |  BMI |
| Nairo Quintana    |    167 |     59 | 29.0 |
| Chris Froome      |    186 |     68 | 19.9 |
| Robert Förstemann |    174 |     90 | 19.9 |
| Conor Dunne       |    204 |     88 | 20.0 |

and notice that the BMI numbers do not make sense. I'd like to recalculate them using the equation BMI = W / (H/100)^2.

How can I fill a column with relative indexing in org-mode? Please also indicate how I can trigger a recalculation if/when I modify a height/weight entry.

Answer 1

I'll add an hline below the headings of the table so that I don't have to worry about the formula trying to change the headings:

| Name              | Height | Weight |   BMI |
|-------------------+--------+--------+-------|
| Nairo Quintana    |    167 |     59 | 21.16 |
| Chris Froome      |    186 |     68 | 19.66 |
| Robert Förstemann |    174 |     90 | 29.73 |
| Conor Dunne       |    204 |     88 | 21.15 |
#+TBLFM: $> = $3 / ( ($2/100.0)^2);%.2f

$> is the last column (it can also be written as $4 in this particular case since it is the fourth column, just like $3 is the third column - see below for other ways to reference these columns that might make more sense, depending on the circurmstances).

You have to start Emacs Calc (M-x calc) before you apply the formula, which you do with C-c C-c somewhere on the formula. And you can recalculate with C-c C-c any time you want.

The formula can also be written by using notation relative to the last column ($> is the last column, $>> is the penultimate one and so on - similarly $< is the first column, $<< is the second column, although that's probably less useful):

#+TBLFM: $> = $>> / ( ($>>/100.0)^2);%.2f

or

#+TBLFM: $> = $-1 / ( ($-2/100.0)^2);%.2f

This latter form can be used for convenient access to middle columns of the table, e.g. if you have 20 columns and you want to have column 10 depend on columns 9 and 8, then use $10 for the result column, but $-1 and $-2 as relative references to the two previous columns. You can also define and use symbolic names for columns - see Advanced Features in the Org mode manual, but I would recommend that you read that after you read the References subsection mentioned below.

See The spreadsheet in the Org mode manual, particularly the References subsection. You'll have to reread that subsection quite a few times: it is quite dense.