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.