LaTeX spreadtab cells reference

 December 28, 2022

When I create LaTeX files containing complex tables with many correlated numbers, I use spreadtab. Overall, I like the package, but it often needs better documentation, not because features are not documented but because it’s hard to find what you are looking for. Cells references are an example of this, but it is a critical topic for this package, so those are the ways I’ve discovered to sum a column of values. To do so, I’ll take the example of page 19 of the package documentation.

Absolute reference

The first way to achieve this is by counting the cells:

\begin{spreadtab}{{tabular}{r@{}r}}
       & 15 \\
    @+ & 37 \\
    @+ & 13 \\
    @+ & 48 \\
    @+ & 19 \\
    \cline{2-2}
       & sum(B1:B5) \\
\end{spreadtab}

This approach is straightforward but very slow since every time you add or remove a line, you must change all formulas. In addition to being slow, it is very error-prone because counting cells is not always easy and can be even more complex based on how you write your source files.

Tags

The way documented on page 19 of the documentation uses tags.

\begin{spreadtab}{{tabular}{r@{}r}}
       & 15tag(foo) \\
    @+ & 37 \\
    @+ & 13 \\
    @+ & 48 \\
    @+ & 19tag(bar) \\
    \cline{2-2}
       & sum(cell(foo):cell(bar)) \\
\end{spreadtab}

This approach leverages spreadtab’s ability to reference cells that contain a tag. From my experiments, this approach does not work with cells containing a copied formula.

Relative reference

The third approach uses a relative reference, documented on page 4 of the documentation.

\begin{spreadtab}{{tabular}{r@{}r}}
       & 15 \\
    @+ & 37 \\
    @+ & 13 \\
    @+ & 48 \\
    @+ & 19  \\
    \cline{2-2}
       & sum([0,-5]:[0,-1]) \\
\end{spreadtab}

Relative references allow a cell to point to another cell based on the relative locations of the two cells to each other, basically by counting the x distance and y distance from the calling cell to the called cell and expressed as [x, y].

Putting all together

The nice part about spreadtab is that it allows you to use all those different ways to reference cells in the same formula.

Although there are exceptions, the way I would create such a table in a real situation is as follows:

\begin{spreadtab}{{tabular}{r@{}r}}
       & 15 \\
    @+ & 37 \\
    @+ & 13 \\
    @+ & 48 \\
    @+ & 19  \\
    \cline{2-2}
       & sum(B1:[0,-1]) \\
\end{spreadtab}

In my opinion, this approach is by far the most readable, maintainable, and flexible.