The interesting thing here isn’t “spreadsheet, but backwards” so much as “spreadsheet as a constraint system”. Classic spreadsheets are basically DAGs: data flows one way and a lot of UX assumptions (and people’s intuition) rely on that. As soon as you allow arbitrary cells to be solved for, you’re in “which variables are free?” land, and most of the confusion in this thread is really about degrees of freedom, not about the math.
One way to make this less surprising might be to flip the default: treat all cells as fixed unless explicitly marked as solver variables, and give a lightweight visualization of “these are the cells that will move if you edit this one.” That keeps the power of a general constraint solver while preserving the mental model spreadsheet users already have, and it opens the door to more serious use cases (financial models, physics, scheduling) without feeling like spooky action at a distance.
show comments
hallole
Phenomenal! This is a solid prototype, clean execution. I've had exactly this idea, too: I've already given the spreadsheet the relationships between these values, why can't it just work backwards when values change? That premise hides a ton of complexity, though, I'm sure. Lots of scary matrices.
show comments
areyousure
I have wanted one general application of this idea in a spreadsheet. Specifically, I track some of my running, including speed (pace), distance, and time. Under different circumstances, I have exactly two of the three available and I want the third to be computed, but it varies which. I have found it fairly difficult to implement this kind of data entry in Google Spreadsheets and Excel, even know conceptually it's a very simple constraint "a*b=c" where I know some two variables.
As a more substantive comment: You may find the thesis "Propagation networks : a flexible and expressive substrate for computation" by Alexey Radul interesting. https://dspace.mit.edu/handle/1721.1/54635
show comments
willrshansen
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.
show comments
Animats
Somebody did this back in the DOS era. The program was sometimes called "the crooked accountant's spreadsheet", because you could start with the outputs you wanted and get the input numbers adjusted to fit.
Anyone remember?
SoftTalker
Can you enter an RSA key and have it produce two prime numbers?
show comments
satvikpendem
Ah, two way data binding. If you've used any frameworks before React (and a couple earlier ones with the same philosophy) you'll understand how it becomes a spaghetti mess over time.
show comments
b-karl
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.
“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.
show comments
amirhirsch
Cool!
Constraint propagation from SICP is a great reference here:
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.
thomastay
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?
show comments
zkmon
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.
A bidirectional formula is also known as an integrity constraint in databases (plus some triggers for restoring the constraint upon updates)!
deckar01
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.
nico
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)
show comments
iamwil
Reminds me of functional logical programming languages like Verse. when you specify the output and ask for the inputs, you get all possible inputs.
Could this easily represent a Kalman filter and other typically complex control problems?
Makes me imagine plotting a inverted pendulum and other real time systems. Could a cell variable be set to Time?
throw310822
Hm? I don't get it.
What's the point of calculating backwards non-invertible operations such as addition? Isn't the result just arbitrary?
show comments
injidup
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.
show comments
aatd86
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.
show comments
WillAdams
Very cool!
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?
lethal-radio
What do you use it for?
digdugdirk
Super cool! Well done. Now take it down and never let Microsoft get their hands on the code, or the entire economic system will go down in flames.
interesting. like Excel Solver? or OpenSolver, Gurobi, other optimizers? or different objective?
show comments
yogeshpaliyal
amazing
d--b
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.
The interesting thing here isn’t “spreadsheet, but backwards” so much as “spreadsheet as a constraint system”. Classic spreadsheets are basically DAGs: data flows one way and a lot of UX assumptions (and people’s intuition) rely on that. As soon as you allow arbitrary cells to be solved for, you’re in “which variables are free?” land, and most of the confusion in this thread is really about degrees of freedom, not about the math.
One way to make this less surprising might be to flip the default: treat all cells as fixed unless explicitly marked as solver variables, and give a lightweight visualization of “these are the cells that will move if you edit this one.” That keeps the power of a general constraint solver while preserving the mental model spreadsheet users already have, and it opens the door to more serious use cases (financial models, physics, scheduling) without feeling like spooky action at a distance.
Phenomenal! This is a solid prototype, clean execution. I've had exactly this idea, too: I've already given the spreadsheet the relationships between these values, why can't it just work backwards when values change? That premise hides a ton of complexity, though, I'm sure. Lots of scary matrices.
I have wanted one general application of this idea in a spreadsheet. Specifically, I track some of my running, including speed (pace), distance, and time. Under different circumstances, I have exactly two of the three available and I want the third to be computed, but it varies which. I have found it fairly difficult to implement this kind of data entry in Google Spreadsheets and Excel, even know conceptually it's a very simple constraint "a*b=c" where I know some two variables.
As a more substantive comment: You may find the thesis "Propagation networks : a flexible and expressive substrate for computation" by Alexey Radul interesting. https://dspace.mit.edu/handle/1721.1/54635
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.
Somebody did this back in the DOS era. The program was sometimes called "the crooked accountant's spreadsheet", because you could start with the outputs you wanted and get the input numbers adjusted to fit.
Anyone remember?
Can you enter an RSA key and have it produce two prime numbers?
Ah, two way data binding. If you've used any frameworks before React (and a couple earlier ones with the same philosophy) you'll understand how it becomes a spaghetti mess over time.
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.
This is pretty cool.
This functionality is called ‘break back’ in a lot of enterprise modelling software. See [IBM TM1](https://www.ibm.com/docs/en/cognos-planning/10.2.1?topic=bre...) and [Anaplan](https://help.anaplan.com/breakback-1b7aa87d-aa13-49f6-8f7d-d...). They generally work in terms of scaling the inputs to match the new output though, which is a bit more basic than this approach.
“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.
Cool!
Constraint propagation from SICP is a great reference here:
https://sicp.sourceacademy.org/chapters/3.3.5.html
Sounds a bit like Prolog with a Spreadsheet UI?
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.
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?
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.
Wow! See the classic https://en.wikipedia.org/wiki/TK_Solver
A bidirectional formula is also known as an integrity constraint in databases (plus some triggers for restoring the constraint upon updates)!
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.
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)
Reminds me of functional logical programming languages like Verse. when you specify the output and ask for the inputs, you get all possible inputs.
you might like https://omrelli.ug/g9/ which is a similar concept but for graphics
Could this easily represent a Kalman filter and other typically complex control problems?
Makes me imagine plotting a inverted pendulum and other real time systems. Could a cell variable be set to Time?
Hm? I don't get it.
What's the point of calculating backwards non-invertible operations such as addition? Isn't the result just arbitrary?
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.
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.
Very cool!
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?
What do you use it for?
Super cool! Well done. Now take it down and never let Microsoft get their hands on the code, or the entire economic system will go down in flames.
What a neat idea!
set A1 = 3 set B1 = 4
set C1 = A1 + B1 = 7
now change C1 = 14 expected A1 = 6 expected B1 = 8
what it did A1 = 7 B1 = 7
great
Excellent (sorry accidental pun)
This is a nice exploration.
interesting. like Excel Solver? or OpenSolver, Gurobi, other optimizers? or different objective?
amazing
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.