1

I would like to input a value in a table, which is added to another existing value in the table and afterwards is reset to 0. I have tried to do this with a column formula, but the problem is, that before any value is used inside another formula, it itself is recalculated, causing the reset to be applied before the value can be used by another formula. I want to do this to save some manual addition in my head and always add numbers correctly.

Here is an example of what I want to do:

#+NAME: example0-working-principle
|   | label | my-value | add |
|---+-------+----------+-----|
| # | name  |      123 |   0 |
|---+-------+----------+-----|
| # | sum   |   123.00 |   0 |
#+TBLFM: $3=$3 + $4;EN::$4=0;::@3$3=vsum(@I..@II);EN%.2f

In this example it seems to always work, that the value in the add column is added to the value in the my-value column.

I have observed, that the order of adding and resetting depends on the number of columns in between the value columns, which shall be added. For example the following still works:

#+NAME: example1-still-working
|   | label | my-value |   |   |   |   |   | add |
|---+-------+----------+---+---+---+---+---+-----|
| # | name  |      174 |   |   |   |   |   |   0 |
|---+-------+----------+---+---+---+---+---+-----|
| # | sum   |   174.00 |   |   |   |   |   |   0 |
#+TBLFM: $3=$3 + $9;EN::$9=0;::@3$3=vsum(@I..@II);EN%.2f

But if I add 1 more column between the my-value and add column, it does not work any longer:

#+NAME: example2-not-working
|   | label | my-value |   |   |   |   |   |   | add |
|---+-------+----------+---+---+---+---+---+---+-----|
| # | name  |      184 |   |   |   |   |   |   |   0 |
|---+-------+----------+---+---+---+---+---+---+-----|
| # | sum   |   184.00 |   |   |   |   |   |   |   0 |
#+TBLFM: $3=$3 + $10;EN::$10=0;::@3$3=vsum(@I..@II);EN%.2f

I have a larger table, with something like 20 formulas, where the add column is reset before it is added to the (further left) value column.

Here is an example of my larger spreadsheet, where it is working:

#+NAME: example3-working
|   | working hours          |    |                     7 |      |      |       |     |
|   | daily business per day |    |                     1 |      |      |       |     |
| # | lunch time             |    |                     0 |    0 |      |       |   0 |
| # | start time             |    | [2021-08-16 Mo 09:40] |    0 |      |       |   0 |
| # | update time            |    | [2021-08-16 Mo 13:20] |    0 |      |       |   0 |
| # | duration in days       |    |                  0.15 |    0 |      |       |   0 |
| # | spent time             |    |              03:36:00 |    0 |      |       |   0 |
| # | overtime hours         |    |                  3.92 |    0 |      |       |   0 |
|---+------------------------+----+-----------------------+------+------+-------+-----|
|   | activity               | ^v |                 done? |    t |    m |    db | add |
|---+------------------------+----+-----------------------+------+------+-------+-----|
| # | a                      |    |                     0 |  124 |    0 |     1 |   0 |
|---+------------------------+----+-----------------------+------+------+-------+-----|
| # | h                      |  4 |                     0 |    0 |    0 |     0 |   0 |
|---+------------------------+----+-----------------------+------+------+-------+-----|
| # | total:                 |    |                     0 |  124 |    0 |   124 |   0 |
| # | in minutes:            |    |                  0.00 |  124 |    0 |   124 |   0 |
| # | in hours:              |    |                  0.00 | 2.07 | 0.00 |  2.07 |   0 |
| # | time left:             |    |                       | 5.93 |      | -1.07 |   0 |
#+TBLFM: $5=$5 + $8;EN::$8=0;::@6$4=date(<@5$4>)-date(<@4$4>);%.2f::@7$4=(60*((60*24*@6$4)-@3$4))+0.1;T::@8$4=5.99-((@1$4 + @2$4)-((@1$4 + @2$4)-@14$5));%.2f;EN::@12$4=vsum(@II..@IIII);EN::@12$5=vsum(@II..@IIII);EN::@12$6=inner(mul,add,@II$5..@IIII$5,@II$6..@IIII$6);EN::@12$7=inner(mul,add,@II$5..@IIII$5,@II$7..@IIII$7);EN::@13$4=inner(mul,add,@II$5..@IIII$5,@II$4..@IIII$4);EN%.2f::@13$5=@12$5;EN::@13$6=@12$6;EN::@13$7=@12$7;EN::@14$4=@13$4/60;EN%.2f::@14$5=@13$5/60;EN%.2f::@14$6=@13$6/60;EN%.2f::@14$7=@13$7/60;EN%.2f::@15$5=(@1$4 + @2$4)-@14$5;EN%.2f::@15$7=@2$4-@14$7;EN%.2f

And here is an example, where it does not work:

#+NAME: example4-not-working
|   | working hours          |    |                     7 |      |      |       |   |   |     |
|   | daily business per day |    |                     1 |      |      |       |   |   |     |
| # | lunch time             |    |                     0 |    0 |      |       |   |   |   0 |
| # | start time             |    | [2021-08-16 Mo 09:40] |    0 |      |       |   |   |   0 |
| # | update time            |    | [2021-08-16 Mo 13:20] |    0 |      |       |   |   |   0 |
| # | duration in days       |    |                  0.15 |    0 |      |       |   |   |   0 |
| # | spent time             |    |              03:36:00 |    0 |      |       |   |   |   0 |
| # | overtime hours         |    |                  3.74 |    0 |      |       |   |   |   0 |
|---+------------------------+----+-----------------------+------+------+-------+---+---+-----|
|   | activity               | ^v |                 done? |    t |    m |    db |   |   | add |
|---+------------------------+----+-----------------------+------+------+-------+---+---+-----|
| # | a                      |    |                     0 |  135 |    0 |     1 |   |   |   0 |
|---+------------------------+----+-----------------------+------+------+-------+---+---+-----|
| # | h                      |  4 |                     0 |    0 |    0 |     0 |   |   |   0 |
|---+------------------------+----+-----------------------+------+------+-------+---+---+-----|
| # | total:                 |    |                     0 |  135 |    0 |   135 |   |   |   0 |
| # | in minutes:            |    |                  0.00 |  135 |    0 |   135 |   |   |   0 |
| # | in hours:              |    |                  0.00 | 2.25 | 0.00 |  2.25 |   |   |   0 |
| # | time left:             |    |                       | 5.75 |      | -1.25 |   |   |   0 |
#+TBLFM: $5=$5 + $10;EN::$10=0;::@6$4=date(<@5$4>)-date(<@4$4>);%.2f::@7$4=(60*((60*24*@6$4)-@3$4))+0.1;T::@8$4=5.99-((@1$4 + @2$4)-((@1$4 + @2$4)-@14$5));%.2f;EN::@12$4=vsum(@II..@IIII);EN::@12$5=vsum(@II..@IIII);EN::@12$6=inner(mul,add,@II$5..@IIII$5,@II$6..@IIII$6);EN::@12$7=inner(mul,add,@II$5..@IIII$5,@II$7..@IIII$7);EN::@13$4=inner(mul,add,@II$5..@IIII$5,@II$4..@IIII$4);EN%.2f::@13$5=@12$5;EN::@13$6=@12$6;EN::@13$7=@12$7;EN::@14$4=@13$4/60;EN%.2f::@14$5=@13$5/60;EN%.2f::@14$6=@13$6/60;EN%.2f::@14$7=@13$7/60;EN%.2f::@15$5=(@1$4 + @2$4)-@14$5;EN%.2f::@15$7=@2$4-@14$7;EN%.2f

Is there a good way to make it always work? Some way to tell it, to first use the value from the add column and then reset it?

Versions

Emacs:

GNU Emacs 27.2 (build 1, x86_64-pc-linux-gnu, GTK+ Version 3.24.24, cairo version 1.16.0)

Org-mode:

Package org is built-in.

     Status: Built-In.
    Version: 9.4.4
    Summary: Export Framework for Org Mode
Required by: ox-rst-20200815.1511, ox-pandoc-20180510.1338, ox-json-20191225.750, ox-asciidoc-20181230.620, ob-typescript-20190910.946, ob-async-20210428.2052
Other versions: 9.4.6 (gnu).
4
  • None of the tables works for me: they all produce identical #ERROR entries in corresponding cells (e.g. in the simple case, the @3$3 cell gives #ERROR in the last evaluation). You probably want @3$3=vsum(@I..@II);EN%.2f for the last formula: I don't think the format you use (;%.2f;EN) is legal (but I may be wrong). And you should turn on formula debugging with C-c { to understand what is going on.
    – NickD
    Aug 16, 2021 at 20:36
  • ... and in one case in the larger table, you have the format specifier in the middle of a formula; that's not allowed, I think. You have to put it at the end.
    – NickD
    Aug 16, 2021 at 20:43
  • The format ...;%.2f;EN has always worked for me and, at a first glance, looks mor natural than not separating the format modifiers, but I may be wrong here as well and it may only accidentally work. It is however the first time I see the format like you specified it ;EN%.2f anywhere. A lack of examples to find may be to blame. I will fix the format in the middle of the formula mistake. Did you try at least the minimum example tables? I am not sure what does not work for you with them. They look pretty basic. How come all those tables work for me and not for you? Aug 17, 2021 at 8:20
  • @NickD Edited. All things you have pointed out should be fixed now. Aug 17, 2021 at 8:39

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.