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).
