1

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.

1 Answer 1

2

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.

3
  • Cheers for that comprehensive answer and those links! I really liked the second suggestion (clean and least likely to trip me up as I only revisit every month or so) so went with that.
    – xdl
    Nov 29, 2022 at 15:42
  • 1
    You might want to check the edit: combining the two formulas into one is simpler and avoids having to do C-c C-c twice.
    – NickD
    Nov 30, 2022 at 3:47
  • 1
    Cheers for the edit; agreed - going with that approach! (but thanks for bringing the multiple tables to my attention - I'm sure I'll find use for them at some other point!)
    – xdl
    Dec 2, 2022 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.