9

The Situation: I read the Org Spreadsheet tutorial but couldn't find an answer. Commonly, I want to have the result of a formula in the last row of a table, i.e., have something like

#+TBLFM: @LAST$2=somefunction(...)

This means that in the formula, I will have to do something like vmean(@2..@LASTBUTONE)

The Problem: Now, this becomes tiring when adding new rows above the last one, because it means I'll have to remember to change the row references.

My Question: Is there a way to reference the last or last-but-one row in a way that doesn't break when adding rows above the last one?

NickD
  • 27,023
  • 3
  • 23
  • 42
andreas-h
  • 1,559
  • 13
  • 22

1 Answers1

11

Two notes to answer your questions.

The first answers your question directly. Take a look at The Org Manual and its section on References.

In short: use @> to refer to the last row.

The example provided in the manual says:

@>$5 [refers to the] field in the last row, in column 5

Referring to the last-to-one row is not possible in this way, as far as I know (but I'd love to be corrected - edit: and I have been, see below). There are, however, many other ways to refer to cells in the table.

My second suggestion is to remember to use the org functions when inserting rows (rather than, for example, copy pasting). The functions are smart and will update the coordinates in your formulas, even when referenced through absolute numbers.

So use org-table-insert-column and org-table-insert-row when adding to your your spreadsheet.

The same goes for shifting rows and columns: use the built in org keybindings and formulas will be updated.


UPDATE: Thanks to @NickD for pointing out that @>> can be used to refer to the second to last row. Works for colmuns as well.

From the manual:

$< and $> are immutable references to the first and last column, respectively, and you can use $>>> to indicate the third column from the right.

EFLS
  • 1,532
  • 10
  • 13
  • 1
    "Referring to the last-to-one row is not possible in this way": it most certainly is: `@>>` is the last-but-one row, `@>>>` is the last-but-two, etc. Similarly for columns: `$>` is the last column, `$>>` is the last-but-one etc. See [Field references](https://orgmode.org/manual/References.html#References). – NickD Sep 21 '18 at 17:46
  • That's awesome. Thanks. I'll update my answer as well. – EFLS Sep 22 '18 at 05:59
  • Also @-1 and $-1 refer to the second-to-last, @-2, etc. – mankoff Sep 22 '18 at 10:43
  • @mankoff I don't think that's entirely correct. The `-1` and `-2` are relative to the current position, according to the manual, and are useful when entering a formula straight into a field with `:=`. – EFLS Sep 22 '18 at 11:29
  • 1
    @EFLS correct... But when entering a formula not in a field (but at `+#TBLFM:`, what do you use to sum columns into the last element? It could be `@>=vsum(@1..@>>)` or `@>=vsum(@1..@-1)`. I've been using the latter, where the `@-1` means 'The second-to-last row'. – mankoff Sep 23 '18 at 09:04
  • @mankoff Ah, thanks. Didn't know that, now the way it is worded in the manual makes more sense. – EFLS Sep 23 '18 at 11:35