orgmode spreadsheet: using an column formula but only between two hline separators

Question

I'm currently keeping track of expenses using an org table; some are billed annually and some monthly and I'd like to eyeball the total monthly number. I used to use something like this:

| Bill           | Monthly | Annual |
|----------------+---------+--------|
| Bill 1         |   10.25 |    123 |
| Horse doctor   |    0.83 |     10 |
| Facebook Prime |    4.17 |     50 |
| Gym            |     115 |        |
| Electricity    |     102 |        |
| Phone Bill     |    3.41 |        |
|----------------+---------+--------|
| Total          |  235.66 |        |
#+TBLFM: @2$2=@2$3/12;%.2f
#+TBLFM: @3$2=@3$3/12;%.2f
#+TBLFM: @4$2=@4$3/12;%.2f
#+TBLFM: @>$2=vsum(@I..@II)

But this is a bit fiddly to add new annual items. Now I'm trying to reduce explictly using cell references by separating out the monthly/annual items with hline separators with something like:

| Bill           | Monthly | Annual |
|----------------+---------+--------|
| Bill 1         |         |    123 |
| Horse doctor   |         |     10 |
| Facebook Prime |         |     50 |
|----------------+---------+--------|
| Gym            |     115 |        |
| Electricity    |     102 |        |
| Phone Bill     |    3.41 |        |
|----------------+---------+--------|
| Total          |  220.41 |        |
#+TBLFM: @I..@II$2=@I..@II$3/12;%.2f
#+TBLFM: @>$2=vsum(@I..@III)

But I'm not sure how to get the first TBLFM (my current attempt) to work so that it applies the monthly calculation but only the cells in the 1st to 2nd hline separator.

Answer 1

Unfortunately, Org mode does not support range formulas with hline-relative coordinates, so things like @I$2..@II$2 = ... will not work. The error message produced is Can't assign to hline-relative references.

The best that you can do along the lines you are suggesting is something like this:

#+TBLFM: @2$2..@4$2 = $3/12;%.2f :: @>$2 = vsum(@I..@III)

using absolute row references for the range formula, with the disadvantage that if you add another bill in the yearly column, you will have to remember to update the final row of the range (e.g. from @4 to @5 in the example above).

An alternative is to instead calculate the total of the yearly column and add 1/12 of that to the total of the monthly column: that gives you the same result as the previous method but fills in a different cell:

#+TBLFM: @>$> = vsum(@I..@II) ::  @>$2=vsum(@II..@III) + @>$> / 12;%.2f

One disadvantage is that you have to press C-c C-c twice to completely update the table; alternatively, use C-u C-u C-c * to recalculate until convergence is achieved. Do C-h i org-table-recalculate for some details and also read the section Updating the table in the manual.

You can also combine the two formulas into one and avoid the repeated recalculation:

#+TBLFM: @>$2=vsum(@II..@III) + vsum(@I$3..@II$3) / 12;%.2f

This is probably the best solution for your (relatively simple) table.

But depending on your needs, you might want to reorganize the table in different ways (see the Advanced features section of the manual for some ideas). You might even want to split your table into multiple simple tables for the raw data and then provide a summary table of results, using remote references to the data tables; e.g.:

#+name: annual
| Bill           | Annual |
|----------------+--------|
| Bill 1         |    123 |
| Horse doctor   |     10 |
| Facebook Prime |     50 |

#+name: monthly
| Bill        | Monthly |
|-------------+---------|
| Gym         |     115 |
| Electricity |     102 |
| Phone Bill  |    3.41 |


#+name: summary
 | monthly outlay |
 |----------------|
 |         235.66 |
#+TBLFM: @> = vsum(remote(annual, @2$2..@>$2)) / 12  + vsum(remote(monthly, @2$2..@>$2));%.2f

Now you can modify the two data tables at will and the summary table will continue to work. N.B. you cannot use hline-relative references for remote tables either: the separate tables allow you the flexibility to modify them at will, without having to worry about multiple hlines.