5 ms·
Excel still doesn't have any way of doing unit tests or confirming that the content is valid and as it should. For example: you can have an Excel file with 100
by theshrike79 3y ago
Excel still doesn't have any way of doing unit tests or confirming that the content is valid and as it should.
For example: you can have an Excel file with 100k lines and have a formula accidentally replaced by a static number on line 87456. Nobody will ever find it.
- dokem 3y agoDoes Excel have version control and diffing? I know Word does.
- jonp888 3y agoA diff tool called "Spreadsheet Compare" is included with Excel. I don't think its supports "Track Changes" like Word.
- HPsquared 3y agoIt supports track changes if you save the file as a "shared workbook". That has a limited feature set, though.
- nullindividual 3y agoWhen stored on SharePoint, yes to version control.
- robertlagrant 3y agoBut it's not useful. Version control on WYSIWYG is definitely not a solved problem.
- wombatpm 3y agoNot in any manner that is useful
- xkcd-sucks 3y agoDo people working in finance / government / other places where Excel heavy fields really not have any tooling for "linting"/"typing" (formula replaced by static number in 1 line, integer displayed as date etc) or "unit testing"? This sounds like an assumption worth validating, because it sounds like an easy product to sell for lots of money.
- theshrike79 3y agoNope. Source: SO works in a field where they handle other people's money and payroll. Not a small mom & pop shop either. The employees use Excel sheets handed over from "someone" and just plug in the numbers. In Britain they messed up the covid numbers because they used the wrong format to store their infection numbers and lost all lines after 65534...
- Tyruiop 3y agoYes. > This sounds like an assumption worth validating, because it sounds like an easy product to sell for lots of money. Unfortunately, it's not so simple. I've worked in exactly this field, getting such institutions/companies to adopt this kind of software is ridiculously hard.
- AndreasHae 3y agoWould love to hear your experience on why that‘s the case. Sounds like an idiot proof value proposition to me, considering the damages that could be avoided.
- tomjakubowski 3y agoIs it really not possible to write unit tests for a spreadsheet with Office Script? > and have a formula accidentally replaced by a static number on line 87456 Surely this specific case could be validated externally with a script too.
- theshrike79 3y agoYes, it's possible to do a VBA macro or an external program to validate a sheet. But there is no built-in tool you can use to say to excel "this column must always be a number, don't ever allow it to be autodetected as a date" or "this column must always have a formula, if the formula doesn't exist or is a static number, alert the user". You can lock cells, but who bothers doing that in the real world.
- elmolino89 3y agoIf one has to write a program to check if a user did not screw up cut and paste from one sheet/column/whatever it would be IMHO easier to just skip Excel, user and just write a script automating error prone C&P in Excel. If some user wants to view it, then the script can create a valid Excel file.
- HPsquared 3y agoThere are certainly a lot of good practices such as ensuring any formulas are compatible with "fill down". It'd make for a nice standard, like MISRA C. If you wanted automatic formula auditing, I'd recommend using R1C1 reference style so formulas using relative references are independent of the cell's location, then use FORMULATEXT function, and an array formula applied over the whole range you want to check. Then you could be sure.
- MonkeyClub 3y ago> If you wanted automatic formula auditing, I'd recommend using R1C1 reference style so formulas That's indeed one of the basic suggestions in Spolsky's "You Suck At Excel": https://www.youtube.com/watch?v=0nbkaYsR94c https://www.youtube.com/watch?v=0nbkaYsR94c
- layer8 3y agoRegarding your example, you can protect worksheets and have only some cells unlocked for modification [1]. You can add conditional formatting, VBA procedures, additional formulas etc. to do whatever validation you like. However, you need a software engineer (or someone who thinks like one) to properly implement that, and it’s not exactly fun. [1] https://support.microsoft.com/en-us/office/lock-or-unlock-specific-areas-of-a-protected-worksheet-75481b72-db8a-4267-8c43-042a5f2cd93a https://support.microsoft.com/en-us/office/lock-or-unlock-sp...
- x3874 3y ago...and in the year 2525 one still cannot avoid having their users mistakingly copy & paste formatting and thus destroy auto-extending Excel tables... There are workarounds using VBA, but not via Typescipt - the Excel JS API knows no clipboard.
- dilap 3y agoHave two different people do the same work, see if they get the same answer. Ideally the two people use different tools, so "likely to make same mistake" is minimized.
- kamikaz1k 3y ago“A man with one watch always knows what time it is. A man with two watches is never sure.”
- gilleain 3y agoOh man. My mother's house has so many clocks with a variety of opinions on what the time is. I have to average them to get an estimate of what the real time is.
- MichaelZuo 3y agoThat's why you need three watches.
- the8472 3y agoEvery man now has access to a swarm of atomic clocks in Earth orbit and the necessary relativistic corrections to know time anywhere on earth, down to a few nanoseconds.
- scrivna 3y agoTell that to my microwave, my oven, the clock in my car, my watch, the clock on the wall…
- miroljub 3y agoThat's why you use SQL and not Excel to analyse the data.
- theshrike79 3y agoWill you be the one to teach boomer-age payroll accountants to create SQL tables for their calculations? I can give you a phone number to offer your consulting services :) Of course a custom website backed by a properly schema'd SQL database would be better, but that takes money, time and someone needs to keep it updated. People use Excel because it's on everyone's computer by default, they have full permissions to do anything with it without needing IT approval and it's surprisingly powerful when you get into it. And if a client happens to have a really weird non-standard requirement for how their payroll is done, any accountant can easily create a custom Excel sheet just for them - instead of waiting 6 months for an external consultant company to provide them with a custom tool that does 80% what they need.
- miroljub 3y agoI understand why people still use Excel, but then, it's to be expected that there will be errors. And that's fine, some errors can be tolerated if the saving for using cheap tools with a cheap workforce is big enough. But if you want reliability, just don't use Excel because making it bulletproof is pricier than just using SQL for reporting and analysis.
- mikehollinger 3y ago> 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 agoStarting 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.
- pjacotg 3y agoI've built a unit testing framework for Excel using Python and the openpyxl library. I work in a bank and using Excel was unavoidable in some cases. You'd basically build a test suite for a spreadsheet in Python and run it like a regular test suite. It helped a lot in catching issues. Obviously it would be nicer if unit testing was built into Excel, but it is possible to build side tools around it.
- riffraff 3y agoI know a guy who worked at $BIGBANK and did the same with Ruby, he gave us a presentation about it at a local user group meetup many years ago. IIRC, he was pretty proud of being able not only to have a regression test suite, but also being able to do TDD-excel :)
- pjacotg 3y agoI completely appreciate that. I was also attempting to tell regular excel users about the virtues of TDD! I had grander plans where I imagined creating centralised test suites that caught more generic issues and anybody in the bank could upload their spreadsheet for validation. It didn't quite get that far though.
- pc86 3y agoI've had columns filled with a formula and intentionally replaced one cell with a static value. You immediately get a warning on that cell that can be ignored/cleared but takes a handful of clicks to do so. Granted, there's no overall place to view the warnings (that I am aware of) so if you have 100k lines and one cell replaced you can't easily see it without inspecting everything. Is this no longer the case?
- theshrike79 3y agoI just made an Excel sheet, put in formulas and I could just type over any formula with numbers without any notifications or errors. How do you manage to require "a handful of clicks" to do it?
- mkishi 3y agoThis only happens in "proper" tables. When you add a formula to a table column, Excel will automatically convert it into a "calculated column," so overridden values will have a green mark denoting an "inconsistent formula." It's still a pretty minor warning and it only requires two clicks to ignore.
- wpietri 3y agoI agree with the general thrust of your point, but I should point out that there are non-technological ways to error-proof. E.g., this pre-computer approach: https://en.wikipedia.org/wiki/Double-entry_bookkeeping https://en.wikipedia.org/wiki/Double-entry_bookkeeping
- rvba 3y agoExcel marks inconsistent formulas, there is also a formula checker.
- jimnotgym 3y agoWell it depends on your data. I'm an accountant and it is very normal for accountants spreadsheets to be full of 'check digits'. Checks and balances are well ingrained into accounts! In its basic form you perform the same calculation on the totals as you did on the roots and subtract it from the result. The answer must be zero. I know it is harder on some kinds of data, but it can prevent a huge amount of every day errors.
- samrus 3y agoMakes perfect sense. The testing being discussed is to create platform where generic tests can be coded and rerun. So tests that aren't specific to totals and subtotals
- Foobar8568 3y agoPower query solves these issues