Show HN: I made a spreadsheet where formulas also update backwards

Original Article ↗ Hacker News Discussion ↗

Concept and Existing Parallels

  • Commenters relate “backwards formulas” to:
    • Database integrity constraints plus triggers.
    • Older constraint-based tools like TK Solver and Borland Eureka.
    • Excel Goal Seek / Solver, enterprise “break back” (IBM TM1, Anaplan), but note that those are usually one-output, or simple proportional scaling, whereas this is pervasive and graph-wide.
    • Constraint networks / propagators (SICP, Radul’s “Propagation networks”) and logic/constraint programming (Prolog, CLP(R)).
    • Graphics/parametric tools like g9.js, CAD sketch constraints, inverse kinematics solvers.

Solver Behavior and Mathematical Issues

  • Central confusion: when a formula is not uniquely invertible (e.g., A+B=C), how does the system pick new A and B when C changes?
  • The implementation is described as a general root-finding solver using interval-based constraint propagation, Newton-like methods, and bisection, working over a composed cell graph.
  • It finds:
    • No solution → report failure.
    • Unique solution → return it.
    • Multiple/infinite solutions → pick one via a heuristic, optimized mainly for speed, not minimal change.
  • Users observe current behavior:
    • For A+B=C, if C is edited, A and B become equal halves, ignoring prior ratio.
    • Some argue the “least-assumption” update would preserve the input ratio (scale both by the same factor).
  • The solver currently does not use previous values as hints, but commenters suggest using past values or user hints/priorities.

UX, Degrees of Freedom, and Constraints

  • Many find the idea powerful but unintuitive without clear control over which cells are free.
  • Current mechanism: cells prefixed with # are constants; others are variables.
  • Several suggest:
    • Invert this default (constants by default, explicitly marked solver variables).
    • Lock icons or other UI affordances.
    • User hints like “prefer to solve for rate, keep principal fixed.”
    • Domain constraints (allowed intervals) and visual cues for degrees of freedom / which cells will move.

Applications, Enthusiasm, and Edge Cases

  • Proposed uses: financial models, engineering/design, pace–distance–time calculations, inverse kinematics, control problems, and as a general “spreadsheet as constraint system.”
  • Some see this as a major missing feature in mainstream spreadsheets; others note commercial tools avoid it due to non-invertible operations and complexity.
  • Users report edge cases where particular target values (e.g., exact 100) fail while nearby values work, and express interest in more details on the solver’s internals and multi-output constraints.