1

Given this table with columns DATE, VALUE and DIFFERENCE (empty):

| <2022-03-06 Sun> | 34 |  |
| <2022-03-07 Mon> | 34 |  |
| <2022-03-08 Tue> | 32 |  |
| <2022-03-09 Wed> | 21 |  |
| <2022-03-10 Thu> | 20 |  |
| <2022-03-11 Fri> | 19 |  |
| <2022-03-12 Sat> | 13 |  |
| <2022-03-13 Sun> | 11 |  |

I want to compute DIFFERENCE, which is the difference between consecutive dates.

| <2022-03-06 Sun> | 34 |     |
| <2022-03-07 Mon> | 34 |   0 | 
| <2022-03-08 Tue> | 32 |  -2 |
| <2022-03-09 Wed> | 21 | -11 |
| <2022-03-10 Thu> | 20 |  -1 |
| <2022-03-11 Fri> | 19 |  -1 |
| <2022-03-12 Sat> | 13 |  -6 |
| <2022-03-13 Sun> | 11 |  -2 |

I can't find a compact field formula that would do this. I have sofar a long formula:

@2$3 = vsum(@2$2-@1$2)
@3$3 = vsum(@3$2-@2$2)
@4$3 = vsum(@4$2-@3$2)
@5$3 = vsum(@5$2-@4$2)
@6$3 = vsum(@6$2-@5$2)
@7$3 = vsum(@7$2-@6$2)
@8$3 = vsum(@8$2-@7$2)

There must be a way to specify the relative positions of cells to find a universal syntax that tackles this problem so one does not have to compute line by line.

UPDATE: with the help of the commenters, I managed to put together this formula thanks for this post:

#+TBLFM:@2$3..@8$3 = @-1$-1 - $-1

However, it results in this:

| <2022-03-06 Sun> | 34 |  0 | 
| <2022-03-07 Mon> | 34 | 33 | 
| <2022-03-08 Tue> | 32 |  0 | 
| <2022-03-09 Wed> | 21 |  0 | 
| <2022-03-10 Thu> | 20 |  0 | 
| <2022-03-11 Fri> | 19 |  0 | 
| <2022-03-12 Sat> | 13 |  0 | 
| <2022-03-13 Sun> | 11 |  0 | 
#+TBLFM:@2$3..@8$3 = @-1$-1 - $-1

@2$3..@8$3: range from row 2 until row 8 column 3

@-1$-1: previous row and one column left from the current field.

$-1: one column left from the current field.

12
  • Did you read the cell references section of the Org mode manual?
    – NickD
    Feb 24, 2022 at 13:24
  • 2
    Does this answer your question? help with org-mode table formula
    – NickD
    Feb 24, 2022 at 14:48
  • 1
    In particular, look for relative references in that answer.
    – NickD
    Feb 24, 2022 at 14:48
  • 2
    It's basically correct: I think you need a space after the colon, otherwise it is not recognized as a table formula. Also, the subtraction is done in the opposite order of that in your long formula above, so you'll get the opposite signs. And I might use the @> and $> notations for the last row and the last column and write it as #+TBLFM: @2$>..@>$> = $-1 - @-1$-1.
    – NickD
    Feb 24, 2022 at 19:23
  • 1
    I would rather have you study the related question in detail and see that it actually answers this question as well. There is an infinite number of questions but if they are similar, I'd rather not have an answer for each one. If you think that the related question is helpful, upvote it - if you don't, then write your own answer to this question. In either case, I made my opinion known when I voted to close this question as a duplicate.
    – NickD
    Feb 25, 2022 at 15:05

0

Your Answer

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

Browse other questions tagged or ask your own question.