Emacs Asked on December 26, 2020
I’m trying to calculate a new column on an org table, I’m using the following table formula:
#+TBLFM: $9=if (@# > 15, ($6-@-14$6)*100000/10724599, 0)
It’s taking the difference on column 6 between the current row and 14 rows previous.
To prevent it from overrunning the start of the table the current row @#
is checked to see there are 14 previous rows to look back over. If not, a zero if filled in.
However when using this formula I get the message:
Row descriptor -14 leads outside table
But it shouldn’t do that as it should only evaluate the true branch when there are >14 previous rows – at least that’s what I thought?
In case Calc formulas don’t respect short-circuiting with if
, I tried a lisp construct too – but get the same result using if
or when
:
#+TBLFM: $9='(if (> @# 15) (/ (* (- $6 @-14$6) 100000) 10724599) 0);N
If I replace the -14
for -1
the logic doesn’t fail – I presume, because of the header.
How can I achieve a lookback in a formula like the example above?
Note – if I comment out the condition that it testing for out of bounds indexes in org-table.el
it works fine – which suggests org-table--row-type
is being called eagerly on all parameters of if
even if they are not used? So perhaps I need to write my own lazy version of if
or similar?
(cond ;;((or (< i 0) (>= i l))
;;(user-error "Row descriptor %s leads outside table" desc))
;; The last hline doesn't exist. Instead, point to last row
;; in table.
((= i (1- l)) (1- i))
(t i))
If I then turn on debugging formula I can see what is happening for the first row – it does look to me like expansion of the out of bound array happens even though I never use it?
With out commenting out the code the debugging does nothing – I assume because the user error is hit before the debugger is started.
Substitution history of formula
Orig: if (@# > 15, ($6-@-14$6)*100000/10724599, 0)
$xyz-> if (@# > 15, ($6-@-14$6)*100000/10724599, 0)
@r$c-> if (2 > 15, ($6-(Cumulative_cases))*100000/10724599, 0)
$1-> if (2 > 15, ((1)-(Cumulative_cases))*100000/10724599, 0)
Result: 0
Format: NONE
Final: 0
Update – it’s not necessary to understand the problem but for reference the full code can be found here:
https://github.com/falloutphil/Misc/blob/master/covid.el
You may well be right about the eager evaluation (which might be a bug). EDIT: Actually, there is no evaluation at all: the user error is precipitated just by pattern matching - while in row 2, it finds the @-14$6
in the formula and proceeds to check it immediately; it does not matter at all that the formula would never hit that. Fixing that is probably difficult (to say the least).
The only thing I can suggest as a workaround is to fill the first 14 rows of column 9 with zeros, and apply the formula unconditionally to the rest of the rows, using range formulas:
#+TBLFM: #+TBLFM: @2$9..@15$9 = 0 :: @16$9..@>$9=($6-@-14$6)*100000/1072459
Correct answer by NickD on December 26, 2020
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP