6 ms·

Excel Labs, a Microsoft Garage Project

104 points by wlkr 3y ago

Closi 3y ago
The advanced formula environment is great, I've been using it for about a year now and it makes LAMDA so much more useful.
heisenzombie 3y ago
There’s something unbeatable about the purity of a spreadsheet. For certain kinds of problem, it’s one of the most understandable ways to communicate a program. But there are the obvious problems, as formulas get inscrutable and you really want some more powerful data types. So I’ve been playing with the new Excel features - lambdas and the new kind of array formulas. And they’re kind of great! I ported some non-trivial analysis algorithms from numpy to excel and it makes for a highly shareable and havkable programming environment for non-coders. There’s all sorts of crazy excel warts (I’m doing maths with complex numbers, and the handling of those is a true “WAT”) It’s kind of almost-great. I can’t put my finger on it but I feel like it’s close to a really winning programming environment for certain kinds of algorithms-transforming-data programs. I think Excel probably has too much baggage to get there, but these experiments are still really interesting.
- nhatcher 3y ago
  Are some of those Excel workbooks with non-trivial analysis and complex numbers something you could share? I'm truly curious.
  - heisenzombie 3y ago
    It's annoying for me to share the spreadsheet, but I'm basically implementing the math in: https://lhc-div-mms.web.cern.ch/tests/MAG/FiDeL/Documentation/lhc-m-es-0001-30-00.pdf https://lhc-div-mms.web.cern.ch/tests/MAG/FiDeL/Documentatio... To do with calculating multipole coefficients from measured data and then doing coordinate transformations on them.
    - nhatcher 3y ago
      Oh wow! Many thanks! It's impressive what you can do on a spreadsheet these days.
- bdavbdav 3y ago
  Coming from finance where we've been trying to wean FO off excel, every time I see things like this, I'm reminded why we shouldn't get comfy relying so heavily on Excel: https://www.businessinsider.com/excel-partly-to-blame-for-trading-loss-2013-2?r=US&IR=T https://www.businessinsider.com/excel-partly-to-blame-for-tr...
  - wslh 3y ago
    It is a grey area, spreadsheets are great but at some point you are building a mammoth with it. I also find Excel, and Google Sheets outdated on its own "form-factor". It would be great to hear from the HN community about ways to improve spreadsheets. For example, adding new types and easier integrations with other systems. Also having views/forms. Lotus Notes was revolutionary in this aspect. Excel is the measure because they have a good engine. Probably we need to create a great DAG engine and put the spreadsheet over it.
    - bradrn 3y ago
      > It would be great to hear from the HN community about ways to improve spreadsheets. I’m working on this! Very early days so far, and I have nothing much to show for it yet, but I do have some ideas on integrating static type inference and proper data structures into spreadsheets. My initial prototype is at [0]: it’s horrifically buggy in just about every way (don’t even try to get it working!), but the screenshot should give some idea of what I’m thinking should be possible. [0] https://github.com/bradrn/nexo https://github.com/bradrn/nexo
    - operator-name 3y ago
      There's https://pyspread.gitlab.io/ https://pyspread.gitlab.io/, which uses python. On the more programmable side you've got Jupyter with https://github.com/ipyflow/ipyflow https://github.com/ipyflow/ipyflow and https://github.com/fonsp/Pluto.jl https://github.com/fonsp/Pluto.jl.
      - pcthrowaway 3y ago
        Grist looks similar, and also interesting: https://www.getgrist.com/ https://www.getgrist.com/ The tables are sqlite tables, so the schema has to be rigid, while I think they also offer views that allow you to make more user-friendly views. They also let you run Python inside the formula
666satanhimself 3y ago
[dead]
rickdeckard 3y ago
Oh, I need to check that out. The universal language and portability of a spreadsheet is really hard to beat. Seeing how people often read xls-sheets by backtracking formulas, it would be great if MS could add support for comments within formulas (multiline formulas being a thing for many years now) That wouldn't blow up the whole complexity of formulas, but still allow at least to explain some of these huge formulas people have to deal with on a daily basis.
- loudmax 3y ago
  > The universal language and portability of a spreadsheet Depends strongly on your definitions of "universal" and "portable". Try reading these files with non-proprietary software.
  - rickdeckard 3y ago
    Yes, very valid comment. Portable in the sense of sharing something across different departments of a company, universal in the sense of a tool that can be understood and used across many disciplines of work.
- aaravchen 3y ago
  As a hack workaround, I drop everything into a `let` block and define "variables" named "comment1", "comment2", etc that are just comment strings and otherwise unused. It affects performance if you're hitting that formula a lot with disparate data, but most of the time anything needing performance from a cell should be using the new arrays processing anyway.
lindbergh 3y ago
Yes Excel lately is turning into a really compelling product, using functions such as sequence (dynamic arrays), lambda (pure functions) and let (defining sub-variables). You'd think you're coding in a pure functional language, and that's great! Now, ff only Excel had a convolution function! But nonetheless it's a great GUI, everybody knows how to use it and sharing it is dead simple. The one downside I can find is the lack of a good plotting library. And yes comments as well is something I miss a lot.
- occamrazor 3y ago
  I have implemented a convolution (using radix-2 FFT) as an intermediate step in Bluestein FFT. Look in the source of: https://github.com/altomani/XL-FFT https://github.com/altomani/XL-FFT
  - lindbergh 3y ago
    Oh wow that's really impressive. Thanks a lot !
bambax 3y ago
Some things in Excel have not improved since 2003, that should be fixed before toying with "AI" . For example, when importing a CSV: - Excel cannot guess the encoding of the file, and relies on the user selecting it from a list that has maybe a hundred values (?!); the most common encoding, UTF-8, is neither at the top or at the bottom of that list, but somewhere near the end, and is called "65001 : Unicode (UTF-8)" (the preceding value is "65000 : Unicode (UTF-7)"). There is little chance non-technical users will get this right the first time, or any time thereafter, and the result is files that are circulated with garbled encoding and wrong values. - Excel cannot guess the separators either! (How hard can it be?) That's probably the reason why one cannot "open" a CSV file directly in Excel and having it displayed properly; one has to go through the whole "import" process. Yet Windows insists all CSVs should automatically open in Excel. Yes, it's a minor thing, but it should be so easy to fix; instead of that, recent versions of Office have brought incredibly annoying animations that take 2-3 screens to disable.
- dagw 3y ago
  Excel cannot guess the separators either! (How hard can it be?) What is the separator in this .csv file ;) 1,2;3,4;5,6 But on a more serious note, I completely agree. There are so many small quality of life things that can and should be fixed in Excel, and it's baffling that they haven't.
  - M4v3R 3y ago
    Sure, there are some edge cases where guessing the separator would be hard, but in 99% of cases you can correctly identify the separator pretty easily and in case you can't you just don't pre-fill that field in the import dialog.
    - PTOB 3y ago
      Psst, the joke is that it's a "comma separated variable" file.
      - cxr 3y ago
2Gkashmiri 3y ago
I'm one of the libreoffice gang and I rely on =regex as a part of my work on a daily basis. Eventually we 'might' get therein terms of new features but for the time being, I am fine. Macros are slowly improving in libreoffice but it still ways off anyway.
null_shift 3y ago
I’m assuming the generative AI functions are not running purely locally and would be sharing your data to Microsoft/OpenAI ?
jbullock35 3y ago
Most of us who use Excel are still using it as though it were 2013. Are there any good tutorials about the major features, like lambdas and dynamic arrays, that have been introduced in the last few years?

104 points by wlkr 3y ago

The advanced formula environment is great, I've been using it for about a year now and it makes LAMDA so much more useful.

heisenzombie 3y ago

There’s something unbeatable about the purity of a spreadsheet. For certain kinds of problem, it’s one of the most understandable ways to communicate a program. But there are the obvious problems, as formulas get inscrutable and you really want some more powerful data types. So I’ve been playing with the new Excel features - lambdas and the new kind of array formulas. And they’re kind of great! I ported some non-trivial analysis algorithms from numpy to excel and it makes for a highly shareable and havkable programming environment for non-coders. There’s all sorts of crazy excel warts (I’m doing maths with complex numbers, and the handling of those is a true “WAT”) It’s kind of almost-great. I can’t put my finger on it but I feel like it’s close to a really winning programming environment for certain kinds of algorithms-transforming-data programs. I think Excel probably has too much baggage to get there, but these experiments are still really interesting.

nhatcher 3y ago
Are some of those Excel workbooks with non-trivial analysis and complex numbers something you could share? I'm truly curious.
- heisenzombie 3y ago
  It's annoying for me to share the spreadsheet, but I'm basically implementing the math in: https://lhc-div-mms.web.cern.ch/tests/MAG/FiDeL/Documentation/lhc-m-es-0001-30-00.pdf https://lhc-div-mms.web.cern.ch/tests/MAG/FiDeL/Documentatio... To do with calculating multipole coefficients from measured data and then doing coordinate transformations on them.
  - nhatcher 3y ago
    Oh wow! Many thanks! It's impressive what you can do on a spreadsheet these days.
bdavbdav 3y ago
Coming from finance where we've been trying to wean FO off excel, every time I see things like this, I'm reminded why we shouldn't get comfy relying so heavily on Excel: https://www.businessinsider.com/excel-partly-to-blame-for-trading-loss-2013-2?r=US&IR=T https://www.businessinsider.com/excel-partly-to-blame-for-tr...
- wslh 3y ago
  It is a grey area, spreadsheets are great but at some point you are building a mammoth with it. I also find Excel, and Google Sheets outdated on its own "form-factor". It would be great to hear from the HN community about ways to improve spreadsheets. For example, adding new types and easier integrations with other systems. Also having views/forms. Lotus Notes was revolutionary in this aspect. Excel is the measure because they have a good engine. Probably we need to create a great DAG engine and put the spreadsheet over it.
  - bradrn 3y ago
    > It would be great to hear from the HN community about ways to improve spreadsheets. I’m working on this! Very early days so far, and I have nothing much to show for it yet, but I do have some ideas on integrating static type inference and proper data structures into spreadsheets. My initial prototype is at [0]: it’s horrifically buggy in just about every way (don’t even try to get it working!), but the screenshot should give some idea of what I’m thinking should be possible. [0] https://github.com/bradrn/nexo https://github.com/bradrn/nexo
  - operator-name 3y ago
    There's https://pyspread.gitlab.io/ https://pyspread.gitlab.io/, which uses python. On the more programmable side you've got Jupyter with https://github.com/ipyflow/ipyflow https://github.com/ipyflow/ipyflow and https://github.com/fonsp/Pluto.jl https://github.com/fonsp/Pluto.jl.
    - pcthrowaway 3y ago
      Grist looks similar, and also interesting: https://www.getgrist.com/ https://www.getgrist.com/ The tables are sqlite tables, so the schema has to be rigid, while I think they also offer views that allow you to make more user-friendly views. They also let you run Python inside the formula

666satanhimself 3y ago

[dead]

rickdeckard 3y ago

Oh, I need to check that out. The universal language and portability of a spreadsheet is really hard to beat. Seeing how people often read xls-sheets by backtracking formulas, it would be great if MS could add support for comments within formulas (multiline formulas being a thing for many years now) That wouldn't blow up the whole complexity of formulas, but still allow at least to explain some of these huge formulas people have to deal with on a daily basis.

loudmax 3y ago
> The universal language and portability of a spreadsheet Depends strongly on your definitions of "universal" and "portable". Try reading these files with non-proprietary software.
- rickdeckard 3y ago
  Yes, very valid comment. Portable in the sense of sharing something across different departments of a company, universal in the sense of a tool that can be understood and used across many disciplines of work.
aaravchen 3y ago
As a hack workaround, I drop everything into a `let` block and define "variables" named "comment1", "comment2", etc that are just comment strings and otherwise unused. It affects performance if you're hitting that formula a lot with disparate data, but most of the time anything needing performance from a cell should be using the new arrays processing anyway.

lindbergh 3y ago

Yes Excel lately is turning into a really compelling product, using functions such as sequence (dynamic arrays), lambda (pure functions) and let (defining sub-variables). You'd think you're coding in a pure functional language, and that's great! Now, ff only Excel had a convolution function! But nonetheless it's a great GUI, everybody knows how to use it and sharing it is dead simple. The one downside I can find is the lack of a good plotting library. And yes comments as well is something I miss a lot.

occamrazor 3y ago
I have implemented a convolution (using radix-2 FFT) as an intermediate step in Bluestein FFT. Look in the source of: https://github.com/altomani/XL-FFT https://github.com/altomani/XL-FFT
- lindbergh 3y ago
  Oh wow that's really impressive. Thanks a lot !

bambax 3y ago

Some things in Excel have not improved since 2003, that should be fixed before toying with "AI" . For example, when importing a CSV: - Excel cannot guess the encoding of the file, and relies on the user selecting it from a list that has maybe a hundred values (?!); the most common encoding, UTF-8, is neither at the top or at the bottom of that list, but somewhere near the end, and is called "65001 : Unicode (UTF-8)" (the preceding value is "65000 : Unicode (UTF-7)"). There is little chance non-technical users will get this right the first time, or any time thereafter, and the result is files that are circulated with garbled encoding and wrong values. - Excel cannot guess the separators either! (How hard can it be?) That's probably the reason why one cannot "open" a CSV file directly in Excel and having it displayed properly; one has to go through the whole "import" process. Yet Windows insists all CSVs should automatically open in Excel. Yes, it's a minor thing, but it should be so easy to fix; instead of that, recent versions of Office have brought incredibly annoying animations that take 2-3 screens to disable.

dagw 3y ago
Excel cannot guess the separators either! (How hard can it be?) What is the separator in this .csv file ;) 1,2;3,4;5,6 But on a more serious note, I completely agree. There are so many small quality of life things that can and should be fixed in Excel, and it's baffling that they haven't.
- M4v3R 3y ago
  Sure, there are some edge cases where guessing the separator would be hard, but in 99% of cases you can correctly identify the separator pretty easily and in case you can't you just don't pre-fill that field in the import dialog.
  - PTOB 3y ago
    Psst, the joke is that it's a "comma separated variable" file.
    - cxr 3y ago

2Gkashmiri 3y ago

I'm one of the libreoffice gang and I rely on =regex as a part of my work on a daily basis. Eventually we 'might' get therein terms of new features but for the time being, I am fine. Macros are slowly improving in libreoffice but it still ways off anyway.

null_shift 3y ago

I’m assuming the generative AI functions are not running purely locally and would be sharing your data to Microsoft/OpenAI ?

jbullock35 3y ago

Most of us who use Excel are still using it as though it were 2013. Are there any good tutorials about the major features, like lambdas and dynamic arrays, that have been introduced in the last few years?