Multi-line org mode formula definitions

Question

Org tables are great! But sometimes, for non-trivial cases, the formulas get a little unwieldy. E.g.:

#+TBLFM: @III+1$3..@IIII-1$>='(let ((l (-non-nil (mapcar* (lambda (w v) (when (equal w $2) (string-to-number v))) (list @II+1$2..@III-1$2) (list @II+1..@III-1))))) (/ (-sum l) (float (length l))));E%.1f

^{(There might be a denser Calc syntax for the above, but that's beside the point.)}

Editing it as it is all cramped in one line is not great. There is also no way to document the formula or its parts.

Is there a way to split the formula into multiple lines?

If not, is there a way to define an Emacs Lisp function within the same file, so that it is evaluated automatically when the .org file is opened?

Answer 1

Org-Babel allows creating functions (written in, but not limited to, emacs-lisp), which can then be referenced in table formulas:

#+NAME: add
#+begin_src emacs-lisp
  (+ a b)
  ; feel free to use as many lines or comments here!
  ; press C-c ' here to edit in emacs-lisp-mode
#+END_SRC

|----+----+-----|
|  a |  b | a+b |
|----+----+-----|
|  1 |  2 |   3 |
| 10 | 10 |  20 |
|  5 | -7 |  -2 |
|----+----+-----|
#+TBLFM: $3='(org-sbe "add" (a $1) (b $2))

However, passing ranges to such functions is problematic (because of some serious shortcomings of the org-sbe function's implementation).

Answer 2

You'll find it in the org manual Multi-line org mode formula definitions .

| x | y |
|---+---|
| 1 |   |
| 2 |   |
#+TBLFM: $2=$1*1
#+TBLFM: $2=$1*2