1

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

Improve this question
4
  • 1
    You might try turning on formula debugging with C-c { and see whether that helps in figuring out what is happening. The same key binding will turn it off afterwards.
    – NickD
    Aug 21, 2020 at 3:08
  • @NickD - I've had limited success with this because the issue happens before the debugger gets off the ground - however if I hack the org-table code as per edits above it does seem to suggest my theory is on the right lines.
    – Phil
    Aug 21, 2020 at 8:25
  • 1
    You may be right that commenting out that check might do the job. What would have to be checked however is not only that the conditional code in the formula works correctly without the check, but also that the unconditional code (getting rid of the if(...)) is treated properly and an error is raised. I'll try that out when I have some time, but you might want to give it a try as well. If that checks out, a bug report and a suggested fix to the mailing list might be in order.
    – NickD
    Aug 22, 2020 at 13:53
  • Yep thanks @NickD - leave a note here if you do mail the list, I'll do the same so we don't duplicate. Whilst the workaround below works well, and the manual, if read carefully (i.e. blink and you miss it!), does elude to this being the idiomatic way - I think at the very least the manual should clarify the behavior of 'if' - I think anyone used to short-circuiting would be surprised by the current behavior - it was a head scratcher for me!
    – Phil
    Aug 22, 2020 at 18:09

1 Answer 1

Reset to default
1

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
Improve this answer

Your Answer

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

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