Sympy can (often) solve under constrained systems in terms of the free variables. The problem I run into is discrete constraints that make solving less closed form and more combinatorial search. When textbook amplifier formulas significantly diverge from physical reality I model the errors as linear correction factors and use gradient descent to correct it in a few experiments, but I’m curious if there is software that has solved this problem.
The first example on the main page has a formula with two variables being updated from changing one value. The immediate question I have is if I change the output, where does the extra degree of freedom come from on the inputs? Does one stay locked in place? Unclear.
I am a huge fan of the concept though. It's been bugging me for years that my spreadsheet doesn't allow editing text fields after filtering and sorting them down to the subset I want. I have to go all the way back to the mess of unsorted input rows to actually edit them.
100% this. When I reached the end of that page I felt pranked because the obvious question was never answered. How are these cases resolved? Is it possible to fix some inputs and only update others? What if I sometimes want to change input A, and other times I want to update input B? All this should be explained as early as possible.
You can do it and it is explained, actually. Use # as a prefix to indicate a constant, e.g.: #50 will be a constant and not a variable.
In the future I'd like to support more user input constraints, in particular domain constraints for variables. So you could tell the solver that this cell must remain in some interval, and it would respect that interval instead of assigning any real value.
My brother once suggested that there are probably bits of code/algorithms that would be world changing if they were released in academic journals, but instead were written by some unknowing programmer in an afternoon for their job coding embedded systems for refrigerators.
This particular example may be unlikely, but it's a very fun idea.
Iirc, Heisenberg reinvented Matrix calculations to solve a problem in quantum physics. Not being a mathematician, he wasn't aware of the concept. Born recognized what Heisenberg had done and introduced him to his own reinvention.
Lots of people working in different fields end up reinventing things that have been known to math for centuries, often in clunky roundabout ways. I imagine some of them figure out things not known to math, but it's far more likely to go the other way.
Primarily because the learnings you make are the same as the original “discoverer”. Without those learnings, you might not be able to arrive at your true destination.
> Lots of people working in different fields end up reinventing things that have been known to math for centuries
I remember reading, about a year or two ago, about a medical doctor that published a paper rediscovering calculus (I just looked it up, it happened in 1994, there’s been many articles and videos about it)
Jokes aside, let's say someone does figure out how to break RSA over a weekend project. The evil options are easy to come up with, but what is the actually responsible, ethical, thing to do? Never tell anyone?
Contact a known and trusted security researcher who can verify to the world that you did what you said you did, so everyone else can have as much time as possible to figure out exactly how fucked they are. Doing nothing isn’t an option; once someone figures something like that out, it signifies that conditions were ripe for the discovery to be made, and it’s only a matter of time before it’s discovered again independently.
“Formulas that update backwards” is the main idea behind neural networks such as LLMs: the computation network produces a value, the error in this value is computed, and then the error quantity is pushed backward through the network; this relies on the differentiability of the function computed at each node in the network.
In Excel you have goal seek for this functionality. I believe it does some form of numerical solving of the equation system.
Good for every situation when you need to solve equations!
In the context of using spreadsheets I think about solving simple financial or maybe construction/mechanical design problems where you don’t want to solve it manually or program it and a spreadsheet is a quick and useful interface.
The idea is very interesting. As a default strategy you could preserve the ratio of inputs by scaling them to match the scaling of the output, instead of making them equal (for addition). Similarly, for multiplication, you could preserve the ratio of inputs as well, by scaling them by nth root of the scaling factor of the output.
This is really cool! It's like Excel's goal seek but can also handle the case of arbitrary input cells. Goal seeek can only handle one input and one output cell.
But how do you handle the case where multiple variables can be changed? If multiple input cells is the key difference from Goal seek, i think some more rigor should be placed into the algorithm here
e.g. setting A1 + B1 and wanting the result to be 5. Currently it bumps both A1 and B1 equally. What's the thought process behind this?
Could you build an inverse kinematics solver with this? (I recently watched a youtube video of someone iteratively working out the solutions for a robotic arm, by alternating modifying the inputs and the results)
That's an interesting example I hadn't thought of. Probably? I'll need to try it. Thank you for the suggestion!
I think one issue will be that trig functions are kinda weird because they are non-injective. So they work but they are awkward (try solving cos(A1) = 0.5). Inverse kinematics is so well studied, you're probably better off using a dedicated algorithm.
A 2d sketcher with constraints is kind of similar. For example the equation
A = B + C
Where A, B, C are the lengths of 3 parallel lines. Within the sketcher you can drag the length of any one of those lines and the other two will adjust to keep the constraints.
Yes! I'd really like to make something graphical in this same idea space next. See g9.js for example, or parametric CAD software like FredCAD which kinda does what you said.
I think the concept is solid. I’ve only had a few minutes of playing with it, but I have the opinion is that from a UX perspective constants are more common than variables. So perhaps a cell containing a constant should not have a #, but a variable should.
You are right that there is some arbitrariness involved when picking a solution, however it's a bit more subtle than that.
Let's say our problem has N free variables.
Step 1 is finding the subset of R^N that is the solution to the root finding problem. If this subset is a point, we are done (return that point). Note that if there is no solution at all bidicalc should correctly report it.
Step 2 is if the solution subset is not a point. Then there is multiple (maybe even an infinity of) solutions, and picking one is indeed arbitrary.
does the algorithm tries to make minimal changes to the free variables ?
If we have 1 + 1 = 2 and change 2 -> 4 then -100000 + 100004 = 4 is also a valid solution.
When I tried it it changed it to 2 + 2 so perhaps there is optimization but also a valid optimization can be minimal free variable changes in which case it would be 1+3 = 4 and we update 1 free variable instead of 2.
I have no idea which is better just curios how it works.
I like the idea very much.
The actual heuristic used to pick a solution from an infinite solution subspace is a bit too complex to explain in a comment. I really need a blackboard :D The main goal was actually to find a solution, any solution at all, and fast. I wanted the backwards update to be very fast to feel as magic as possible. So the heuristic is pretty simple and could definitely be improved!
> Even a normal spreadsheet is fairly complex beast. But the novel thing about bidicalc is the backwards solver. Mathematically, updating a spreadsheet "backward" is a (potentially underdetermined) root finding problem, because we are trying to find a vector of unknowns such that , where F is the function computed by the cells formulas, and G is the objective value entered in the cell. Note that F is not necessarily a single formula, but the result of composing an upstream graph of cells into a single function.
> The actual root-finding solver is a custom algorithm that I made. It a general purpose algorithm that will find one root of any continuous-almost-everywhere function for which a complete syntactic expression is known. It uses a mix of continuous constraint propagation on interval union arithmetic , directional Newton's method and dichotomic search. It is of course limited by floating point precision and available computation time.
But that really doesn't answer your question. I see no reason why the solver wouldn't decide every time it had a two-variable summation that ADD(X+Y) doesn't reverse to X=-90 and Y=100.
I'd love to see a version where cells are "torn off" and named as they were in Lotus Improv and one had a "formula pane" where one could see all the formulae for a spreadsheet.
Would it be possible to create this in Python so that it could be a part of pyspread?
Never used any of those, so I don't know! I'd be curious to read a comparison from anyone who knows about them.
I think what's pretty unique about the bidicalc solver that I made is that it does not depend on the previous input values to update backwards. It's truly solving the root finding problem. The advantage is that there are never any "stuck in a local optimum" problems with the solver. So you can solve difficult problems like polynomials, etc.
The examples are great and these bidirectional calculators are something that people would love to have in traditional spreadsheets.
So much so that Credit Suisse, which basically was running everything on heavily modded Excel, created a full language whose outputs were Excel spreadsheets capable of doing that. That thing called “paradise” was a total monstrosity but showed how much people wanted this.
That said, you really need a way to set which cells are fixed and which cells are allowed to move if you want to move past basic examples.
Most times you know what you want to do. like => if the user modifies that cell, find a solution for those specific ones.
If you can enter that info, then you have a lot more constrains for your solver and will avoid a lot of edge cases where everything goes to 0, and you can check that the calculation entered is indeed reversible or not, or if it could have multiple solutions, and so on.
LOL! Gemini suggested to implement this to me literally yesterday: bidirectional computations.
The example was that given a temperature in Celsius and Fahrenheit, modifying either of them should update their counterpart.
In angular that would be two linked signals for instance, but even that is a bit fringe. Gemini was going for something even more elaborated.
I told Gemini that spreadsheets were actually not doing that and that I had ways to implement that behavior without the complexity.
Just writing that to show the rabbit hole people are going to fall into if they let their llms go brrr. ;D
In any case, the problem is interesting.
The point was to include bi-directionality inside a graph of computations so that we didn't get bogged down by cycles.
The benefit being that it would handle float precision issues.
My more manual solution expect that floats precision issues are handled explicitly. I think that this level of explicitness is needed anyway for proper floating point error mitigation.
To not do what, to not implement a constraint solver for bidirectional formulas?
If you input my above comment it is for sure going to weigh the pros and cons.
https://gemini.google.com/share/f40bf53d9c21
Excerpt from the initial convo with gemini:
Thinking with 3 Pro December 11, 2025 at 09:59 PM
2. Propagators (Constraint Networks)The Problem: Your valueref binder and watch logic handle one-way data flow well. But complex forms (e.g., "Start Date must be before End Date," or "Fahrenheit <-> Celsius") require messy, cyclic event handlers to keep everything in sync.The Academic Concept: Propagators (Alexey Radul / Gerald Sussman, MIT).Instead of functions ($A \rightarrow B$), you define Constraints. A network of constraints seeks a consistent value for all connected nodes. It is "multi-directional" by design.
I see my message above being downvoted, I don't even know why ;D
In the end Gemini did agree that it was not necessary to introduce this level of complexity for my use case.
To be fair, Victor goes further because he adds a solver on top.
In the research of a solution that might make sense. The issue in general is that not everything has a reverse operation so, in a sense, it is but an approximation.
I am a huge fan of the concept though. It's been bugging me for years that my spreadsheet doesn't allow editing text fields after filtering and sorting them down to the subset I want. I have to go all the way back to the mess of unsorted input rows to actually edit them.
In the future I'd like to support more user input constraints, in particular domain constraints for variables. So you could tell the solver that this cell must remain in some interval, and it would respect that interval instead of assigning any real value.
This particular example may be unlikely, but it's a very fun idea.
Primarily because the learnings you make are the same as the original “discoverer”. Without those learnings, you might not be able to arrive at your true destination.
Luckily no one is suggesting that.
I remember reading, about a year or two ago, about a medical doctor that published a paper rediscovering calculus (I just looked it up, it happened in 1994, there’s been many articles and videos about it)
This is such a great story
This is also true with patents.
The term of interest is "backpropagation".
Wasn’t Prolog invented to formalise these kinds of problems of making the inputs match what the desired output should be.
[1] https://en.wikipedia.org/wiki/Declarative_programming
Good for every situation when you need to solve equations!
In the context of using spreadsheets I think about solving simple financial or maybe construction/mechanical design problems where you don’t want to solve it manually or program it and a spreadsheet is a quick and useful interface.
But how do you handle the case where multiple variables can be changed? If multiple input cells is the key difference from Goal seek, i think some more rigor should be placed into the algorithm here
e.g. setting A1 + B1 and wanting the result to be 5. Currently it bumps both A1 and B1 equally. What's the thought process behind this?
Constraint propagation from SICP is a great reference here:
https://sicp.sourceacademy.org/chapters/3.3.5.html
I think one issue will be that trig functions are kinda weird because they are non-injective. So they work but they are awkward (try solving cos(A1) = 0.5). Inverse kinematics is so well studied, you're probably better off using a dedicated algorithm.
A = B + C
Where A, B, C are the lengths of 3 parallel lines. Within the sketcher you can drag the length of any one of those lines and the other two will adjust to keep the constraints.
What's the point of calculating backwards non-invertible operations such as addition? Isn't the result just arbitrary?
You are right that there is some arbitrariness involved when picking a solution, however it's a bit more subtle than that.
Let's say our problem has N free variables.
Step 1 is finding the subset of R^N that is the solution to the root finding problem. If this subset is a point, we are done (return that point). Note that if there is no solution at all bidicalc should correctly report it.
Step 2 is if the solution subset is not a point. Then there is multiple (maybe even an infinity of) solutions, and picking one is indeed arbitrary.
> Even a normal spreadsheet is fairly complex beast. But the novel thing about bidicalc is the backwards solver. Mathematically, updating a spreadsheet "backward" is a (potentially underdetermined) root finding problem, because we are trying to find a vector of unknowns such that , where F is the function computed by the cells formulas, and G is the objective value entered in the cell. Note that F is not necessarily a single formula, but the result of composing an upstream graph of cells into a single function.
> The actual root-finding solver is a custom algorithm that I made. It a general purpose algorithm that will find one root of any continuous-almost-everywhere function for which a complete syntactic expression is known. It uses a mix of continuous constraint propagation on interval union arithmetic , directional Newton's method and dichotomic search. It is of course limited by floating point precision and available computation time.
But that really doesn't answer your question. I see no reason why the solver wouldn't decide every time it had a two-variable summation that ADD(X+Y) doesn't reverse to X=-90 and Y=100.
The user hints principle is preferred fixed so they can see what rate is needed for a givem amount of interest.
Hints could have a precedence order (then prefer to fix earlier terms on an operation on a tie breaker.)
I'd love to see a version where cells are "torn off" and named as they were in Lotus Improv and one had a "formula pane" where one could see all the formulae for a spreadsheet.
Would it be possible to create this in Python so that it could be a part of pyspread?
[1] https://x.com/gothburz/status/1999124665801880032
This is a nice exploration.
I think what's pretty unique about the bidicalc solver that I made is that it does not depend on the previous input values to update backwards. It's truly solving the root finding problem. The advantage is that there are never any "stuck in a local optimum" problems with the solver. So you can solve difficult problems like polynomials, etc.
So much so that Credit Suisse, which basically was running everything on heavily modded Excel, created a full language whose outputs were Excel spreadsheets capable of doing that. That thing called “paradise” was a total monstrosity but showed how much people wanted this.
That said, you really need a way to set which cells are fixed and which cells are allowed to move if you want to move past basic examples.
Most times you know what you want to do. like => if the user modifies that cell, find a solution for those specific ones.
If you can enter that info, then you have a lot more constrains for your solver and will avoid a lot of edge cases where everything goes to 0, and you can check that the calculation entered is indeed reversible or not, or if it could have multiple solutions, and so on.
I'd like to add more constraints in the future like a domain constraint for variables.
I told Gemini that spreadsheets were actually not doing that and that I had ways to implement that behavior without the complexity.
Just writing that to show the rabbit hole people are going to fall into if they let their llms go brrr. ;D
In any case, the problem is interesting. The point was to include bi-directionality inside a graph of computations so that we didn't get bogged down by cycles. The benefit being that it would handle float precision issues.
My more manual solution expect that floats precision issues are handled explicitly. I think that this level of explicitness is needed anyway for proper floating point error mitigation.
Excerpt from the initial convo with gemini: Thinking with 3 Pro December 11, 2025 at 09:59 PM
2. Propagators (Constraint Networks)The Problem: Your valueref binder and watch logic handle one-way data flow well. But complex forms (e.g., "Start Date must be before End Date," or "Fahrenheit <-> Celsius") require messy, cyclic event handlers to keep everything in sync.The Academic Concept: Propagators (Alexey Radul / Gerald Sussman, MIT).Instead of functions ($A \rightarrow B$), you define Constraints. A network of constraints seeks a consistent value for all connected nodes. It is "multi-directional" by design.
I see my message above being downvoted, I don't even know why ;D
In the end Gemini did agree that it was not necessary to introduce this level of complexity for my use case.
To be fair, Victor goes further because he adds a solver on top. In the research of a solution that might make sense. The issue in general is that not everything has a reverse operation so, in a sense, it is but an approximation.