Thanks for the question. I should first point out we are constantly improving our optimizations and thus things are always changing, so what holds today might not tomorrow.
At a high level Coda does understand the dependencies between each piece of data in your document. The references in a formula and the rows selected from a lookup help build up our understanding of dependencies within the document.
When static data changes we end up looking through the dependencies to figure out which formulas need to be recalculated, those calculations can also trigger more recalculations if they themselves have dependencies.
Now there is a fair bit of complexity that goes into deciding if we can recalculate a single cell in a column verse the entire column. I could probably write a longer blog post on this, but I’ll try to outline an example where this comes up. Imagine a scenario where you have Products which have a Name, a lookup column against a Category table, and Cost. In a Categories table you have want to sum the cost of all products in that category. You could do this a number of ways but lets consider two options:
A) TotalCost= Products.Filter(Category=thisRow).Cost.Sum()
B) Items = Products.Filter(Category=thisRow)
TotalCost = Items.Cost.Sum()
Now let us consider what happens when we update the Cost of one product in both scenarios.
In option A we know the TotalCosts column depends on the Category and Cost column in the Products table. For every category we need to extract the Cost of every product associated with them and sum it. The problem here is without remembering which products belong to a given category we have to recompute the filter for every row in order to determine which category is associated with the product whose Cost changed. This is far from ideal.
You might be able to see why option B performs better now. When the cost is updated we know that only the rows, where the given product is present in Items need to be updated. There is one giant caveat here that sufficiently complicated setups can throw off optimizations and fall back to performance similar to option A.
We do have some work planned on our backlog to automatically detect and optimize option A into option B, but until that happens option B should always perform better.