~/problems / Graphs / Cycle detection

Spreadsheet: show the circular reference

easy ~15 min

A small spreadsheet app refuses to recalculate when formulas refer to each other in a loop. Just saying "circular reference!" isn't helpful, so the error message should show the loop, e.g. B2 -> C7 -> B2.

Write find_loop(formulas) -> list[str]:

  • formulas maps a cell name to the list of cells its formula reads, e.g. {"A1": ["B1", "C1"]} means A1's formula uses B1 and C1. A cell that isn't a key (or maps to []) holds a plain value and reads nothing.
  • If there is a loop, return one as a list [c1, c2, ..., ck] of distinct cells where each c(i) reads c(i+1) and ck reads c1. Any loop, starting from any of its cells, is accepted. A cell that reads itself is the loop [c].
  • If there is no loop, return [].
find_loop({"A1": ["B1"], "B1": ["C1", "D1"], "C1": ["A1"]})
# ["A1", "B1", "C1"]   (or ["B1", "C1", "A1"], or ["C1", "A1", "B1"])
find_loop({"A1": ["B1", "C1"], "B1": ["D1"], "C1": ["D1"]})
# []   two formulas reading D1 is not a loop
find_loop({"X9": ["X9"]})
# ["X9"]

Constraints: up to 500 cells with formulas and 10,000 references in total, so plain recursion is fine (Python's default limit is about 1,000 frames). Cells a formula reads may repeat in its list. One test is a big sheet with many ways to reach the same cells: a search that re-explores cells it has already fully checked takes exponential time.

Show hint

use the three-colour DFS from Basics, and also keep the current DFS path as a list. When you step onto a grey cell (one that's on the path), the loop is the slice of the path from that cell to the end.

Topic: Cycle detection. Visited / on-stack sets; symlinks, CNAMEs, instruction loops.

Read the visual guide
0:00
Ctrl ' run · Ctrl ↵ submit
esc