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

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.

rahimnathwani · 2 months ago

  Does one stay locked in place? Unclear.

If you set C1=A1+B1 then, when you set a value for C1, A1 and B1 are each half of that value, even if they started off unbalanced.

geon · 2 months ago

It would make more sense to preserve the ratio if possible.

idiotsecant · 2 months ago

What is inputs a,b,c,d,and e are polynomial coefficients? I am hoping to get a fields medal plz respond.

exe34 · 2 months ago

I think it would be good if you could lock one of them.

ximm · 2 months ago

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.

fouronnes3 · 2 months ago

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.

Can you enter an RSA key and have it produce two prime numbers?

spiderice · 2 months ago

A random tool like this would be the most entertaining possible way for something like that to be unleashed on the world

jamestimmins · 2 months ago

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.

fouronnes3 · 2 months ago

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?

fainpul · 2 months ago

In Prolog you can write rules (similar to functions in other languages) so that they work "both ways". Let's say you have this rule that defines how pace ("runner's speed") relates to distance and time:

  :- use_module(library(clpr)).

  pace(Km, Minutes, Pace) :-
    { Minutes = Km * Pace }.

Even though the rule only specifies how Minutes are calculated, Prolog can now also calculate the other values.

You can query it, giving unknowns an uppercase `Name`, and it will give you the possible values for it:

  pace(5, 24.5, Pace)
  pace(40, Min, 5)
  pace(Km, 24.5, 5)
  pace(Km, Time, 5)

You can try it here: https://swish.swi-prolog.org/

So if you had a rule that defines RSA key calculation this way, you could enter a key and get all valid solutions for the primes. But of course complex calculations still take a long time. I assume it's similar to a brute force attack in that way (Prolog has clever strategies to explore the solution space though).

Disclaimer: I'm not an expert in Prolog or cryptography, so this might not be 100% accurate.

rvba · a month ago

I always thought the government has it, perhaps in a form of a rainbow table

yonatan8070 · 2 months ago

Or enter a public key + some encrypted data to get the private key

pedrozieg · 2 months ago

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.

That's great feedback, thanks! I agree with you, but I don't want to flip the default because this is an experiment I made for fun, and the whole point is to lean in to the chaos a little bit. In a serious product the UX would definitely need a lot more work though.

abakker · 2 months ago

Graphically, I really like the way autodesk makes sketches in fusion 360 blue until they are fully constrained, and then they are black. My intuition here is that you could color code “degrees of freedom” and “locked” states so that it was more intuitive.

willrshansen · 2 months ago

areyousure · 2 months ago

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

You could create a table with 3 columns: distance, time, pace. Set the display format for time and pace to "Duration".

Enter these formulas:

  distance = time / pace
  time = distance * pace
  pace = time / distance

Drag fill everything down. At this point you get reference errors, but once you enter any two values (thereby overwriting the formulas in those cells), you get your result.

culi · 2 months ago

You just need two spreadsheet tabs. One for the "raw" input and one with a formula that either takes the input if it exists or falls back to the calculated version

davexunit · 2 months ago

Came here to see if anyone mentioned propagators. That thesis is excellent. I second the recommendation.

SoftTalker · 2 months ago

b-karl · 2 months ago

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.

graemep · 2 months ago

This is very different in practice, because it is pervasive rather than something you have to set up for particular cases.

If this was usual it would help a lot with people's tendency to hard code the correct answer rather than fix formulae. Just that aspect of it would be a huge improvement. People do this all the time with simple financial problem, for example.

A lot of what people use spreadsheets for is not all that simple. Again, especially with financial applications. People manage huge amounts of money using horribly complex models implemented in Excel.

moron4hire · 2 months ago

It does not. It perturbates the variables and uses a hill-climbing algorithm.

Interesting. Note that the backwards solver of bidicalc does not use the previous input values of variables at all.

thomastay · 2 months ago

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?

fragmede · 2 months ago

Yeah. The UI could have a lock icon to set, eg B1 should stay fixed and then only A1 would change.

tkzed49 · 2 months ago

It supports this. If you type # before a number, like #5, it's made constant.

amirhirsch · 2 months ago

Cool!

Constraint propagation from SICP is a great reference here:

https://sicp.sourceacademy.org/chapters/3.3.5.html

I wasn't aware of this chapter, but I did use constraint propagation for the solver (among other things), thanks!

adonovan · 2 months ago

“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.

big-chungus4 · 2 months ago

"Formulas that update backwards" isn't really the main idea behind neural networks. It's an efficient way of computing gradients, but there are other ways. For example forward propagation would compute a jacobian-matrix product of input wrt output with an identity matrix. Backpropagation is similar to bidi-calc to the same extent as it is similar to many other algorithms which traverse some graph backward.

I think you should be able to use bidi-calc to train a neural net, altough I haven't tried. You'd define a neural net, and then change it's random output to what you want it to output. However as I understand it, it won't find a good solution. It might find a least squares solution to the last layer, then you'd want previous layer to output something that reduces error of the last layer, but bidi-calc will no longer consider last layer at all.

uoaei · 2 months ago

All those words and you forget to provide people the breadcrumbs to learn more for themselves.

The term of interest is "backpropagation".

Towaway69 · 2 months ago

Won’t another breadcrumb be Prolog and “declarative programming”[1].

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