4 ms·

If Excel had just a couple more features it could be much more ergonomic and not even need macros. Specifically: anonymous functions/lambdas made with just pure

If Excel had just a couple more features it could be much more ergonomic and not even need macros. Specifically: anonymous functions/lambdas made with just pure formulas. Define a function in a cell like `=($s) => [expr]` (or something idc). Call it with `=A1("string arg", B2:B100)` or by name if you name it via named ranges. Functions are obviously a new data type, so you can pass them as parameters to each other or to relevant builtins. It would turn Excel into some kind of hybrid pure functional/spatial language where the code itself is spatial data.

dan-robertson 8y ago
I think I disagree entirely. The typical things one gains from higher order functions are things like mapping and filtering of data structures. Excel only has one data structure—the table. Mapping is done by writing the formula once and then dragging it from the corner to the whole column. Filtering is not really done. Normally use the gui to hide the rows to be filtered away. Functions add in questions of scoping. How are closures supposed to work? Could there be some way to say “the function in cell A1 at the time and closing over the state of when cell B2 was last logically computed”? Excel handles scoping in the non-function-in-cell approach using $. Obviously this all breaks apart when you don’t want tables where you drag things either always down or always across
- aaronblohowiak 8y ago
  Filtering is very much done in sheets I have seen and used
  - dan-robertson 8y ago
    Yes but it’s not done with a function like filter in scheme or Haskell, it’s done by the gui, outside of the world of formulas. For more complex stuff there are database features hidden away inside
- kccqzy 8y ago
  > Mapping is done by writing the formula once and then dragging it from the corner to the whole column. Filtering is not really done. Normally use the gui to hide the rows to be filtered away. You should look into array formulas (or block formulas). They break out of the one-at-a-time mold and could be much more powerful.
- Q6T46nT668w6i3m 8y ago
  Excel doesn’t support mapping over columns or rows?
oomem 8y ago
Doesn't Excel now support Power Query M which is similar to F# and supports lambdas (and much more)? https://support.office.com/en-us/article/create-power-query-formulas-in-excel-8e5e49a2-01c2-496e-b639-41f98f163238 https://support.office.com/en-us/article/create-power-query-... https://msdn.microsoft.com/en-us/query-bi/m/understanding-power-query-m-functions https://msdn.microsoft.com/en-us/query-bi/m/understanding-po... https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-reference https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-re...

dan-robertson 8y ago

I think I disagree entirely. The typical things one gains from higher order functions are things like mapping and filtering of data structures. Excel only has one data structure—the table. Mapping is done by writing the formula once and then dragging it from the corner to the whole column. Filtering is not really done. Normally use the gui to hide the rows to be filtered away. Functions add in questions of scoping. How are closures supposed to work? Could there be some way to say “the function in cell A1 at the time and closing over the state of when cell B2 was last logically computed”? Excel handles scoping in the non-function-in-cell approach using $. Obviously this all breaks apart when you don’t want tables where you drag things either always down or always across

aaronblohowiak 8y ago
Filtering is very much done in sheets I have seen and used
- dan-robertson 8y ago
  Yes but it’s not done with a function like filter in scheme or Haskell, it’s done by the gui, outside of the world of formulas. For more complex stuff there are database features hidden away inside
kccqzy 8y ago
> Mapping is done by writing the formula once and then dragging it from the corner to the whole column. Filtering is not really done. Normally use the gui to hide the rows to be filtered away. You should look into array formulas (or block formulas). They break out of the one-at-a-time mold and could be much more powerful.
Q6T46nT668w6i3m 8y ago
Excel doesn’t support mapping over columns or rows?

oomem 8y ago

Doesn't Excel now support Power Query M which is similar to F# and supports lambdas (and much more)? https://support.office.com/en-us/article/create-power-query-formulas-in-excel-8e5e49a2-01c2-496e-b639-41f98f163238 https://support.office.com/en-us/article/create-power-query-... https://msdn.microsoft.com/en-us/query-bi/m/understanding-power-query-m-functions https://msdn.microsoft.com/en-us/query-bi/m/understanding-po... https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-reference https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-re...