4 ms·

> Excel still doesn't have any way of doing unit tests or confirming that the content is valid and as it should. I make this point when chatting with new team

> Excel still doesn't have any way of doing unit tests or confirming that the content is valid and as it should. I make this point when chatting with new team members that say "let's just use X," without really understanding how X works, and I use Excel as the analogy. Most people know how to use an equation field. Most people don't consider writing a spreadsheet programming, but it is. And just like other types of programs, you can have really insidious bugs, and you need to consider how you'll address those, or address changes in the underlying or internal components of the system. A famous one is that floating point math in excel can give you different answers for "is this the same as that" depending on how you write the equation, for example.

enjo 3y ago
Starting in 2003 I spent several years as a principal developer developing mobile office software. When we launched our excel compatible spreadsheet we diligently formalized every function and wrote tests to verify behavior. It was beautiful. Then we started receiving lots of bug reports from users because our calculations often didn’t match what they were seeing in Excel. Older Excel versions had lots of bugs and Microsoft had been carrying them forward because at that point it was “better” to carry the errors forward and display results people expected rather than fixing them and having everyone confused by the changed results. So we copied their behavior as best we could. I wish I had the list still but it was long and full of weird edge cases which caused formulas to be incorrect.
- lfconsult 3y ago
  Incredible story.
  - emeril 3y ago
    yeah, that's a classic read
- virgulino 3y ago
  Just as Excel carried the Lotus 123 bugs. https://www.joelonsoftware.com/2006/06/16/my-first-billg-review/ https://www.joelonsoftware.com/2006/06/16/my-first-billg-rev...
- peteradio 3y ago
  How did this work out in the end? I imagine it was difficult to decide whether copying bugs was the correct choice.
  - enjo 3y ago
    It absolutely was. At the end of the day our product was measured against fidelity to Excel… so any deviance was always bad for us. I believe Google made the same choice with Google Sheets even.
_dain_ 3y ago
>A famous one is that floating point math in excel can give you different answers for "is this the same as that" depending on how you write the equation, for example. Is this specific to Excel? Floating point math can do that regardless of the platform. Addition on floats isn't associative!
- mikehollinger 3y ago
  > Is this specific to Excel? No. And yet they still have situations where excel itself will differ from ieee 754. See https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result https://learn.microsoft.com/en-us/office/troubleshoot/excel/...

enjo 3y ago

Starting in 2003 I spent several years as a principal developer developing mobile office software. When we launched our excel compatible spreadsheet we diligently formalized every function and wrote tests to verify behavior. It was beautiful. Then we started receiving lots of bug reports from users because our calculations often didn’t match what they were seeing in Excel. Older Excel versions had lots of bugs and Microsoft had been carrying them forward because at that point it was “better” to carry the errors forward and display results people expected rather than fixing them and having everyone confused by the changed results. So we copied their behavior as best we could. I wish I had the list still but it was long and full of weird edge cases which caused formulas to be incorrect.

lfconsult 3y ago
Incredible story.
- emeril 3y ago
  yeah, that's a classic read
virgulino 3y ago
Just as Excel carried the Lotus 123 bugs. https://www.joelonsoftware.com/2006/06/16/my-first-billg-review/ https://www.joelonsoftware.com/2006/06/16/my-first-billg-rev...
peteradio 3y ago
How did this work out in the end? I imagine it was difficult to decide whether copying bugs was the correct choice.
- enjo 3y ago
  It absolutely was. At the end of the day our product was measured against fidelity to Excel… so any deviance was always bad for us. I believe Google made the same choice with Google Sheets even.

_dain_ 3y ago

>A famous one is that floating point math in excel can give you different answers for "is this the same as that" depending on how you write the equation, for example. Is this specific to Excel? Floating point math can do that regardless of the platform. Addition on floats isn't associative!

mikehollinger 3y ago
> Is this specific to Excel? No. And yet they still have situations where excel itself will differ from ieee 754. See https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result https://learn.microsoft.com/en-us/office/troubleshoot/excel/...