5 ms·
> Excel has actual programming affordances now In case the Excel devs read this, one thing I've always wanted is the ability to define a new function by referr
by captainmuon 4y ago
> Excel has actual programming affordances now
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.)
- rad_gruchalski 4y agoYou can also use lambdas: =LAMBDA(... args, calculation) Those can be defined under Formulas > Name manager.
- g8oz 4y agoWill LAMBDA be available for the standalone Office 2021 though? Or is it only going to be there for the subscrption based Office 365?
- deleted 4y ago[deleted]
- downboots 4y agoCustom formulas/functions exist using VBA
- hwayne 4y agoIt'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.
- _dain_ 4y agoUse the LET formula. It gives you lexically scoped variables inside a formula. =LET(foo, ComplicatedExpression(), bar, AnotherComplicatedExpression(), baz, AnExpressionInvolvingPreviousVariables(foo, bar), ExpressionThatIsTheReturnValue()) Excel now also has (will have?) a LAMBDA formula for defining your own functions.
- MikusR 4y agohttps://www.microsoft.com/en-us/research/blog/lambda-the-ultimatae-excel-worksheet-function/ https://www.microsoft.com/en-us/research/blog/lambda-the-ult...
- tlb 4y agoMy 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). Fizzbuzz example: https://visibot.com/webdemo/#/%2Fexamples%2Ffizzbuzz.vb https://visibot.com/webdemo/#/%2Fexamples%2Ffizzbuzz.vb Use right and left arrow keys to scrub through the program's execution. The help link in the NE corner gives more details. (This is still evolving, and uses WASM to run in the browser so it's desktop only. LMK if it doesn't work for you.)
- Someone 4y agoThat’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 https://stackoverflow.com/a/16296990)
- molsongolden 4y agoGoogle Sheets also has custom functions via Apps Script (including autocomplete and function help text).