How a spreadsheet actually evaluates

Before any formula makes sense, you need a model of what the application does when you press Enter. Almost every 'mystery' error in a workbook is really a misunderstanding of evaluation order.

The dependency chain

A spreadsheet is not a document with numbers in it. It is a directed graph. Each cell that contains a formula is a node with edges pointing at the cells it reads. When you change one cell, the application walks that graph and recalculates only the nodes downstream of your change.

This matters practically. If a total looks stale, the question is never "did it save?" — it is "is this cell actually downstream of the number I changed?" Nine times out of ten, someone has typed a hard number over a formula and severed the edge.

Why circular references are fatal

If A1 reads B1 and B1 reads A1, the graph has a cycle and there is no valid order to evaluate them in. The application cannot resolve it, so it stops and warns you. Iterative calculation exists to force an answer, but in a financial model it will happily converge on a number that is confidently wrong.

=SUM(B2:B13)          // reads 12 cells, one edge each
=B14*1.075            // downstream of B14
=B14*1.075+100        // hard number: still fine
=SUM(B2:B14)          // B14 is inside its own range → cycle