In case the Excel devs read this, one thing I've always wanted is the ability to define a new function by referring to cells containing a calculation. Not in a separate window, but in sheet, so you can use multiple cells to break it up nicely.
A B C
+-------------------------
1 | =VARIABLE(X)
2 | =A1*2
3 | =A2+1
And then you could define `MYFUN()` to be A3 and use `=MYFUN(6)` in a cell to get 13. Or maybe, `=EVALUATE(A3; A1)` or `=EVALUATE(A3; 'X')` or something.
I often have complicated, multi step calculations. Currently I copy-paste them for every row (and Excel helps with keeping the formulas in sync). But it would be great if you could do it once on an example (maybe even on another sheet) and then turn it into a custom formula.
(I used to think ancient Excel 4 macros were that from what it looked like, but unfortunately they are something completely different.)
It's not a perfect solution to your problem, but you can simplify multistep calculations with LET. So it would be:
LET(X, A1, Y, X*2, Y+1)
I just checked and the Evaluate Formula button can sequentially step through a LET. Though this doesn't give you intermediate results you can use in other calculations.
My experimental spreadsheet does something like this. You can refer to the value of a cell given some different values in other cells, and it calculates the value of the cell given just those changes. Syntax is cellname{othercell=othervalue, ...}.
So you can use a group of cells like a function with parameters (with defaults).
That’s more or less how Lotus-123 did that kind of thing.
As others said, you can use lambda, but the ‘old modern way’ to do that is to write a function in VBA and call that (https://stackoverflow.com/a/16296990)
In case the Excel devs read this, one thing I've always wanted is the ability to define a new function by referring to cells containing a calculation. Not in a separate window, but in sheet, so you can use multiple cells to break it up nicely.
And then you could define `MYFUN()` to be A3 and use `=MYFUN(6)` in a cell to get 13. Or maybe, `=EVALUATE(A3; A1)` or `=EVALUATE(A3; 'X')` or something.I often have complicated, multi step calculations. Currently I copy-paste them for every row (and Excel helps with keeping the formulas in sync). But it would be great if you could do it once on an example (maybe even on another sheet) and then turn it into a custom formula.
(I used to think ancient Excel 4 macros were that from what it looked like, but unfortunately they are something completely different.)