Live data from Hacker News

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

victorpoughon.github.io

1–10 of 122 posts

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

#1
Hello HN! I'm happy to release this project today. It's a bidirectional calculator (hence the name bidicalc).

I've been obsessed with the idea of making a spreadsheet where you can update both inputs and outputs, instead of regular spreadsheets where you can only update inputs.

Please let me know what you think! Especially if you find bugs or good example use cases.

Show HN: I made a spreadsheet where formulas also update backwards
victorpoughon.github.io

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

#5
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.

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

#6
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?

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

#8

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?

They said this:

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

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

#9

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?

I made this mostly as a fun challenge :)

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.

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

#10
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.

Post reply on HN