This is something that always bugged me about spreadsheets because I can’t find an easy way to do this without resorting to scripting. I know this can be done with scripting, but then it’s just easier for me to implement it in something else as well.
In all the spreadsheet software I’ve seen, I can define a numeric cell’s value to be result of a formula, or I can type in a scalar value, but cannot figure out how to define a cell’s value to be the last modified of (a) result of a formula or (b) typed in value.
To give a concrete example, let’s say I’m using Excel, and I want to make a spreadsheet converting from inches to centimeters and back. Let’s say I want to use only two numeric cells. If you type the number of inches into cell A1, cell A2 will get updated with the number of centimeters. If I type the number of centimeters into cell A2, the cell A1 will be updated with the number of inches. Is this possible somehow without scripting?
Best regards,
No it wouldn’t have to. It can easily build a directed dependency graph of all nodes, and on every update, and traverse the graph without traversing into any cycle. As long as you make “Inserted value” and “Formula” two distinct things that can exist in a cell at the same time, you can resolve it correctly.
Spreadsheets aren’t designed to do what you describe easily but you can trick them into it. It is possible to do I think but it takes more cells than you describe to hold working variables. You can have a data entry cell and then another cell that calculates takes that entry and puts a timestamp of the last update down to the second. The result cells can have some type of if/then logic applied to run through the right logic and give the result you want. It is fairly easy to do for the type of simple example you gave but it could be difficult for more complicated scenarios.
In computing theory, that is a bad design, because you are confusing input and output cells. (Which is probably one reason Excel doesn’t do it easily.)
A couple of better designs:
A1 enter a number in inches, and B1 shows the result in centimeters.
A2 enter a number in centimeters, and B2 shows the result in inches.
or
in A1, enter the number, in B1 either “in” or “cm”, and C1 shows the converted result.
I don’t think you can make a solid argument that mixing input and output cells is somehow theoretically bad. Especially when I am defining a relation between two values that is one-to-one and onto (theoretically, in practice, it’s not guaranteed to be one-to-one because of floating point precision). There is nothing inherent in the relation that gives it any sort of directionality. Directionality should be added at my discretion – if it makes sense in context. In this context, it does not make sense.
If you enter something into either cell it’ll over right the formula, but the other cell will run it’s formula.
I don’t have Excel on this computer so I can’t test it out, but my concerns would be that A)I don’t know what would happen before number is entered in one of the cells and B)Once you enter a number, the formula is gone forever. So you can’t now enter a number in the other cell and have the first cell run the formula (that’s now gone).
Our OP is *not *describing how Excel *does *work. He’s describing how he wishes some theoretical other spreadsheet-like app *could *work.
The idea that a spreadsheet-like app could be designed such that any given cell can hold both an explicit value *and *a formula (and its implicit value) simultaneously is not, on its face, silly. The OP’s example is an obvious use case.
I can see that it’d be easy for the user to inadvertently construct bad logic; even more easily than in a traditional spreadsheet.
In the case of a real current spreadsheet app, it’s always possible to derive the true unique precedence graph of a sheet, and to reliably detect any and all cycles. This uniqueness ensures that any given set of inputs always produces the same output, while the guaranteed cycle detection ensures you won’t get stuck in an infinite loop
At the arm-waving level of analysis, I’m gonna speculate that the precedence graph of such a mixed-use spreadsheet would not be necessarily unambiguously computable. Unless each cell had a flag for which mode it was in at the moment, entered value or calc.
It’d be easy to design the app such that when a value was keyed in, that set the cell into value mode, while still retaining the formula for use if/when the cell was reset to calc mode. But the hard part would be identifying which other cells that had previously been in value mode should now flip into formula mode.
For the trivial single-cell reflexive F-to-C calculator the OP posits, computing this mode-dependency graph would be trivial. but I’m gonna bet patholoical cases are just around the corner. Unless you also added a separate mechanism to specify manually which cells should switch to calc mode when any given cell switches to value mode. But you’d still need at least cycle (and ideally gap) detection to have a reliable system.
My bottom line: We might be able to build such an app, but for any use case more complex than a reflxive units converter app it’d be harder for the end-user to use than an approach which takes input values in via one set of cell(s), any conditional logic settings (e.g. perform C-to-F or perform F-to-C) via another set of cell(s), and reports the result(s) in a third set of cell(s).