The Tyranny of Spreadsheets
June 26, 2021 10:59 PM Subscribe
From a 13th c. merchant's annoyance at bookkeeping to VisiCalc to the recent UK Govt 'misplacing' 16,000 Covid cases, spreadsheets are the swiss army knife of data. A chapter adaptation from Tim Harford's podcast Cautionary Tales, an exploration of Excel and its limits.
No that's right, it's confusing because double entry bookkeeping is confusing. More simply you can replace credit and debit with give or receive. Give (credit) cash and receive (debit) wool.
Your total assets are unchanged. You've traded 100 florins for 100 florin-equivalent sheep.
posted by muddgirl at 11:44 PM on June 26, 2021 [18 favorites]
Your total assets are unchanged. You've traded 100 florins for 100 florin-equivalent sheep.
posted by muddgirl at 11:44 PM on June 26, 2021 [18 favorites]
One of the systems I deployed into the offices in some emerging countries was an "upgrade" to the spreadsheet / database system which, to me at least, was mildly amusing. This would be old hat to anyone who works with data but it was eye opening to me when I first got into it.
So spreadsheets are inherently 2 dimensional data storage - you identify a piece of data by its location in 2 dimensional space - its row and column, like what we used in physical sheets of paper in the 13th century. Basic databases (like in Microsoft Access) are just an extension of that, with data stored in 2 dimensional tables.
Things get ugly when data needs to be described along more than 2 dimensions. Say customer, date, and product type. Customer already occupies the rows and date already occupies the columns - the typical solution is to add a "dummy" column for product type that doesn't contain data itself, but merely elaborates on the data in that row, and you'd need to duplicate the rows for as many types of product as that one customer ordered - for example, if a customer ordered 7 types of products, then you need to duplicate that customer 7 times, one for each product. The other solution is to create more tables: use customer as row, product type as column, then create a new table every new week you are in operation.
The actual proper solution, is to create a 3 dimensional data cube: so you can have customer along one axis, date along the second axis, and product type along the third axis, so every "cell" of data in 3 dimensional space is located and described by their position along those 3 axes.
But why stop at 3? So we typically stored and analyzed data in an 8 dimensional hypercube. Having data stored this way allows retrieval and analysis at incredibly quick speeds, it's several orders of magnitude faster than a typical 2 dimensional data table. I suppose, imagine the row or table duplication required in 3 dimensional data storage, and then imagine the level of duplication required in 8 dimensional data storage - insane.
posted by xdvesper at 11:56 PM on June 26, 2021 [7 favorites]
So spreadsheets are inherently 2 dimensional data storage - you identify a piece of data by its location in 2 dimensional space - its row and column, like what we used in physical sheets of paper in the 13th century. Basic databases (like in Microsoft Access) are just an extension of that, with data stored in 2 dimensional tables.
Things get ugly when data needs to be described along more than 2 dimensions. Say customer, date, and product type. Customer already occupies the rows and date already occupies the columns - the typical solution is to add a "dummy" column for product type that doesn't contain data itself, but merely elaborates on the data in that row, and you'd need to duplicate the rows for as many types of product as that one customer ordered - for example, if a customer ordered 7 types of products, then you need to duplicate that customer 7 times, one for each product. The other solution is to create more tables: use customer as row, product type as column, then create a new table every new week you are in operation.
The actual proper solution, is to create a 3 dimensional data cube: so you can have customer along one axis, date along the second axis, and product type along the third axis, so every "cell" of data in 3 dimensional space is located and described by their position along those 3 axes.
But why stop at 3? So we typically stored and analyzed data in an 8 dimensional hypercube. Having data stored this way allows retrieval and analysis at incredibly quick speeds, it's several orders of magnitude faster than a typical 2 dimensional data table. I suppose, imagine the row or table duplication required in 3 dimensional data storage, and then imagine the level of duplication required in 8 dimensional data storage - insane.
posted by xdvesper at 11:56 PM on June 26, 2021 [7 favorites]
I must have been one of the last people to use paper spreadsheets when I blagged a job in Time-Life International in Amsterdam as a 1979 Summer intern helping formulate the budget for the next financial year. We'd crank out tables of money values allocated by month to each global division of the corporation. . . . then make photocopies and send faxes, so that the management could have an informed discussion. They would come back with changes: "Reduce the spend in Africa over the Summer by 10%, but keep the row and column totals the same by allocating to surplus equally between the Middle East and South America." The geographical spreadsheet would have to be reconciled with the product spreadsheet so eventually the Vice-President of Sales in Singapore would know how much they had available to promote the Great Generals series across AsiaPac.
Large sums of money would be bandied about at head office in Fairfax VA and four of us quants would have to recalculate everything from the broad-brush scribbles, changes and counter-changes that the top guns were thrashing out. It was good fun; with stressful bursts of activity interspersed with cubicle-world idleness and chatter. We all answered to a fearsome buzz-cut Texan who had been to Business School - you could tell because he could operate a desk-calculator with three fingers. But to do this he had to take a deep breath . . . tense his jaw . . . position his hand just so . . . and . . . key-punch! We could beat him one-fingered if there were fewer than 5 numbers to sum. I think its true that were were tasked to have any calculations done in duplicate by different people.
posted by BobTheScientist at 12:47 AM on June 27, 2021 [13 favorites]
Large sums of money would be bandied about at head office in Fairfax VA and four of us quants would have to recalculate everything from the broad-brush scribbles, changes and counter-changes that the top guns were thrashing out. It was good fun; with stressful bursts of activity interspersed with cubicle-world idleness and chatter. We all answered to a fearsome buzz-cut Texan who had been to Business School - you could tell because he could operate a desk-calculator with three fingers. But to do this he had to take a deep breath . . . tense his jaw . . . position his hand just so . . . and . . . key-punch! We could beat him one-fingered if there were fewer than 5 numbers to sum. I think its true that were were tasked to have any calculations done in duplicate by different people.
posted by BobTheScientist at 12:47 AM on June 27, 2021 [13 favorites]
I must have been one of the last people to use paper spreadsheets when I blagged a job in Time-Life International in Amsterdam as a 1979 Summer intern helping formulate the budget for the next financial year.
In around 1995 I "lost out" on a job for an Ontario rum distributer because I didn't know the maximum number of indexes a Jet database could have. The job was inputting reams of sales data distributed in huge paperback books - page after page, column after column. So no you would not have been one of the last dealing with paper spreadsheets. It was a case that the data was being 'protected' by sheer inconvenience of paper transmission. There are probably still some poor fuckers out there in weird offices using paper spreadsheets because they passed some dumb and unimportant gotcha question in an interview with a terrible boss for a terrible job. Hell we found out this pandemic that there were health agencies reporting data by fax! I would be only slightly surprised if I found out that there were still cuneiform tablets in use in some regional offices somewhere because there just wasn't money for upgrades and some central office boss who said "it's worked fine up to now"
posted by srboisvert at 1:44 AM on June 27, 2021 [10 favorites]
In around 1995 I "lost out" on a job for an Ontario rum distributer because I didn't know the maximum number of indexes a Jet database could have. The job was inputting reams of sales data distributed in huge paperback books - page after page, column after column. So no you would not have been one of the last dealing with paper spreadsheets. It was a case that the data was being 'protected' by sheer inconvenience of paper transmission. There are probably still some poor fuckers out there in weird offices using paper spreadsheets because they passed some dumb and unimportant gotcha question in an interview with a terrible boss for a terrible job. Hell we found out this pandemic that there were health agencies reporting data by fax! I would be only slightly surprised if I found out that there were still cuneiform tablets in use in some regional offices somewhere because there just wasn't money for upgrades and some central office boss who said "it's worked fine up to now"
posted by srboisvert at 1:44 AM on June 27, 2021 [10 favorites]
No that's right, it's confusing because double entry bookkeeping is confusing. More simply you can replace credit and debit with give or receive. Give (credit) cash and receive (debit) wool.
And the reason for this confusion is that double-entry bookkeeping predates our understanding of negative numbers. The idea that the duality between debit and credit could somehow be identified with an opposition between positive and (hypothetical, controversial) negative numbers would only be realised slowly, over the course of centuries.
posted by trotz dem alten drachen at 3:00 AM on June 27, 2021 [19 favorites]
And the reason for this confusion is that double-entry bookkeeping predates our understanding of negative numbers. The idea that the duality between debit and credit could somehow be identified with an opposition between positive and (hypothetical, controversial) negative numbers would only be realised slowly, over the course of centuries.
posted by trotz dem alten drachen at 3:00 AM on June 27, 2021 [19 favorites]
Summer 1983, I am the clerk for a tractor dealership. Besides using a typewriter from the 1920's that the owner had found in his barn, I also kept all the books. One account went wayyyy off the rails, so I flipped back through the big book and discovered that I had been adding the page number to the debit column...
posted by Mogur at 4:31 AM on June 27, 2021 [25 favorites]
posted by Mogur at 4:31 AM on June 27, 2021 [25 favorites]
Not to derail, but the fax has always had its place transferring medical information - and, if anything, it’s getting more important. There are multiple “paperless” health information systems (HIS) systems but EPIC doesn’t communicate well with Meditech, nor either with eCinicalWorks, Greenway, etc. not to mention in-house systems used by Quest and so on. And that is just the health data side. Every office, hospital, facility, and so on has an accounting/billing system whose intercommunication with the HIS and other office systems is, well, a work in progress. So, printed orders, reports, invoices, insurance forms, and so on are handed to patients, faxed to vendors, doctors’ offices, and labs, and filed in banks of cabinets still and, ime, in increasing volume. I believe you could use an index of paper use (reams per day) as a indicator of HIS adoption. Or, maybe, faxes per day might work as well.
posted by sudogeek at 5:18 AM on June 27, 2021 [4 favorites]
posted by sudogeek at 5:18 AM on June 27, 2021 [4 favorites]
Spreadsheets are great for screwing around with, but as soon as you let untested, unversioned spreadsheets into production, you're in huge trouble. Especially when they talk to databases. Microsoft's MDS plugin for Excel allows end users to directly edit (meaning break) production SQL Server databases. The ODBC stuff allows people to easily lock up servers, depending on the schema. I've seen spreadsheets with 30-50 connections to the same database. I've had to rip out ones that were using flaky screen scraping to get data when the service had a perfectly good API, because web scraping is the easy option.
posted by kersplunk at 6:23 AM on June 27, 2021 [5 favorites]
posted by kersplunk at 6:23 AM on June 27, 2021 [5 favorites]
The first spreadsheet I encountered was Lotus 123 1A in the early to mid eighties. I believe this was the initial release. But even then you could see there were going to be problems. The design apparently didn't use a sparse matrix array. If you entered a single character into the lower right cell on an otherwise empty sheet, it would eat up all the memory on your poor little DOS box and freeze...
posted by jim in austin at 6:36 AM on June 27, 2021 [5 favorites]
posted by jim in austin at 6:36 AM on June 27, 2021 [5 favorites]
My daughter was just telling us yesterday that she amazed a couple of coworkers (govt office in Midwest) recently by showing them that Excel does math. Apparently these people have been using Excel for 20 odd years thinking it was just a grid of text entries.
posted by COD at 7:02 AM on June 27, 2021 [21 favorites]
posted by COD at 7:02 AM on June 27, 2021 [21 favorites]
Those coworkers had a distressing complete lack of understanding of what a spreadsheet is, for people who have been using one for 20 years! :(
posted by eviemath at 7:19 AM on June 27, 2021 [6 favorites]
posted by eviemath at 7:19 AM on June 27, 2021 [6 favorites]
The article unfairly attributes all the blame to Excel and none to user error.
Scientists should triple check their data. Programmers should validate data and do error handling. And users of Excel are responsible for learning the most basic things about it for flurk's sake. The article is remiss in not explaining that the leading zero issue with the phone numbers, as well as the issue with gene names being turned into dates, could be easily remedied by simply changing the data types of those cells. Excel tries to figure out for you what data type should be set, but obviously isn't always correct.
posted by Flock of Cynthiabirds at 7:34 AM on June 27, 2021 [4 favorites]
Scientists should triple check their data. Programmers should validate data and do error handling. And users of Excel are responsible for learning the most basic things about it for flurk's sake. The article is remiss in not explaining that the leading zero issue with the phone numbers, as well as the issue with gene names being turned into dates, could be easily remedied by simply changing the data types of those cells. Excel tries to figure out for you what data type should be set, but obviously isn't always correct.
posted by Flock of Cynthiabirds at 7:34 AM on June 27, 2021 [4 favorites]
Excel tries to figure out for you what data type should be set
Just doing it instead of asking the user whether it should do it is an Excel error.
posted by GCU Sweet and Full of Grace at 7:46 AM on June 27, 2021 [23 favorites]
Just doing it instead of asking the user whether it should do it is an Excel error.
posted by GCU Sweet and Full of Grace at 7:46 AM on June 27, 2021 [23 favorites]
No that's right, it's confusing because double entry bookkeeping is confusing. More simply you can replace credit and debit with give or receive. Give (credit) cash and receive (debit) wool.
And this is why I got As in every math course I took, but squeeked by with a C in Accounting: it's totally counterintuitive to math folks.
posted by MrGuilt at 8:16 AM on June 27, 2021 [7 favorites]
And this is why I got As in every math course I took, but squeeked by with a C in Accounting: it's totally counterintuitive to math folks.
posted by MrGuilt at 8:16 AM on June 27, 2021 [7 favorites]
Just doing it instead of asking the user whether it should do it is an Excel error.
Debatable. Computer programs do tons of things without explicitly asking the user whether they should be done or not. A program that asked about every single thing would be unusable. Some folks would no doubt complain if Excel forced them to specify the data type of each cell. The user has to bear some responsibility for learning the program, and for checking their results.
posted by Flock of Cynthiabirds at 8:17 AM on June 27, 2021 [3 favorites]
Debatable. Computer programs do tons of things without explicitly asking the user whether they should be done or not. A program that asked about every single thing would be unusable. Some folks would no doubt complain if Excel forced them to specify the data type of each cell. The user has to bear some responsibility for learning the program, and for checking their results.
posted by Flock of Cynthiabirds at 8:17 AM on June 27, 2021 [3 favorites]
I've been in process engineering design of one kind or another since the late 90s and my first job had a drafting department that still had a couple of pencil and paper guys to manage legacy sheets that hadn't migrated over to CAD. One of the main processes had a vellum sheet 24x36" or whatever it was that was entirely a hand drafted spreadsheet of process data, and once a year I'd have to sit and reconcile the process map so they could update the table. Even then I asked if we could move it into software but apparently "we tried that once and it didnt' work." what it was tried on or how it didn't work was never explained to me. The guy I worked with had a particular way he sharpened his particular pencil to make his particular letters, all in aid of supporting readability now but also ease of eraseability later. Needless to say I was not allowed to touch that part.
Anyone here heard of vpplanner? this was a 1-2-3 knockoff we used in first year uni computing classes in 1990 (on old monocrhome PC ATs) I have never come across anyone else who has heard of it.
When i was first married in 95 my wife had a disk copy of quattropro which was the first wysiwyg interface I think I ever dealt iwth in a spreadsheet. We did our family bookkeping in it for years, using a series of keystroke macros I banged together after reading the honest to god paper manual. after a couple three years it got too big and crashed and the thought of rebuilding it or forking it just exhausted me, so we started fresh wtih Quicken (back when they weren't especially SAAS based evil).
posted by hearthpig at 8:32 AM on June 27, 2021
Anyone here heard of vpplanner? this was a 1-2-3 knockoff we used in first year uni computing classes in 1990 (on old monocrhome PC ATs) I have never come across anyone else who has heard of it.
When i was first married in 95 my wife had a disk copy of quattropro which was the first wysiwyg interface I think I ever dealt iwth in a spreadsheet. We did our family bookkeping in it for years, using a series of keystroke macros I banged together after reading the honest to god paper manual. after a couple three years it got too big and crashed and the thought of rebuilding it or forking it just exhausted me, so we started fresh wtih Quicken (back when they weren't especially SAAS based evil).
posted by hearthpig at 8:32 AM on June 27, 2021
also: the matt parker book "humble pi" referenced in the article is pretty great if you like that kind of thing.
posted by hearthpig at 8:33 AM on June 27, 2021 [3 favorites]
posted by hearthpig at 8:33 AM on June 27, 2021 [3 favorites]
I am convinced that every major corporation more than 15 years old (and probably some of the newer ones) have at least one major critical business function that relies on some crufty excel spreadsheet that's been incrementally updated for over a decade by a string of workers who are all terrified to touch it because of apocryphal tales about that time someone screwed it up and they had to rebuild the entire employee database or something.
posted by rmd1023 at 8:46 AM on June 27, 2021 [5 favorites]
posted by rmd1023 at 8:46 AM on June 27, 2021 [5 favorites]
Also, I'm pretty sure there were older CPAs as late as the late 90's who never got the hang of computers and still used paper spreadsheets.
posted by rmd1023 at 8:46 AM on June 27, 2021
posted by rmd1023 at 8:46 AM on June 27, 2021
The article unfairly attributes all the blame to Excel and none to user error.
Even Jeff Goldblum would hesitate throwing an end-user data entry tool, a calculation engine, a database, and a Turing-complete language together into a teleporter.
posted by kersplunk at 8:58 AM on June 27, 2021 [7 favorites]
Even Jeff Goldblum would hesitate throwing an end-user data entry tool, a calculation engine, a database, and a Turing-complete language together into a teleporter.
posted by kersplunk at 8:58 AM on June 27, 2021 [7 favorites]
→
Ah, good old Paperback Software's VP-Planner. Possibly better than Lotus 1-2-3, but better known because it's the company Lotus sued into oblivion over look-and-feel. Paperback was an Adam Osborne joint: whether he genuinely couldn't catch a break or deliberately flubbed them, I don't know, but it's a shame that he's mostly remembered as a punchline.
I remember stumbling into an office supplies store in the heart of Glasgow in the late 1990s and found that they still sold traditional bound ledgers with marbled edges. I suspect there were still some legal offices using them even then
posted by scruss at 9:10 AM on June 27, 2021 [1 favorite]
Anyone here heard of vpplanner?
Ah, good old Paperback Software's VP-Planner. Possibly better than Lotus 1-2-3, but better known because it's the company Lotus sued into oblivion over look-and-feel. Paperback was an Adam Osborne joint: whether he genuinely couldn't catch a break or deliberately flubbed them, I don't know, but it's a shame that he's mostly remembered as a punchline.
I remember stumbling into an office supplies store in the heart of Glasgow in the late 1990s and found that they still sold traditional bound ledgers with marbled edges. I suspect there were still some legal offices using them even then
posted by scruss at 9:10 AM on June 27, 2021 [1 favorite]
Years ago, the bookkeeper at the family business was forced to move from paper to computer for spreadsheets (daily numbers, etc.). She did them all on paper then individually typed the numbers into the cells of the spreadsheet. I naturally died a little when I saw her doing this. It still makes me sad all these years later.
posted by jabah at 9:16 AM on June 27, 2021 [3 favorites]
posted by jabah at 9:16 AM on June 27, 2021 [3 favorites]
I would be only slightly surprised if I found out that there were still cuneiform tablets in use in some regional offices somewhere because there just wasn't money for upgrades and some central office boss who said "it's worked fine up to now"
I swear to Marduk, if that Ea-nasir fucks me on one more shipment of copper...
posted by TheWhiteSkull at 9:18 AM on June 27, 2021 [34 favorites]
I swear to Marduk, if that Ea-nasir fucks me on one more shipment of copper...
posted by TheWhiteSkull at 9:18 AM on June 27, 2021 [34 favorites]
The reason that Excel is in the wrong is because messing with formatting doesn't just change how data is displayed, it literally rewrites the data.
For example if you type March1 into a cell and Excel assumes that is a date, it replaces March1 with a count of the number of seconds from some date in the 1970s to 2020/03/01 and crucially there is no way to go back and recover the original entry. Once this change is made, changing the formatting from date/general to text doesn't reverse the error.
posted by muddgirl at 9:27 AM on June 27, 2021 [36 favorites]
For example if you type March1 into a cell and Excel assumes that is a date, it replaces March1 with a count of the number of seconds from some date in the 1970s to 2020/03/01 and crucially there is no way to go back and recover the original entry. Once this change is made, changing the formatting from date/general to text doesn't reverse the error.
posted by muddgirl at 9:27 AM on June 27, 2021 [36 favorites]
"....my baby just wrote me a ledger"
posted by thelonius at 9:30 AM on June 27, 2021 [7 favorites]
posted by thelonius at 9:30 AM on June 27, 2021 [7 favorites]
Sigh. If you are actually typing in a value, you should notice that it has changed. But we're mostly not talking about that, we're talking about computer programs that interact with each other improperly. Those 64,000 rows were not lost by someone typing in values, they were lost by bad programming.
posted by Flock of Cynthiabirds at 9:48 AM on June 27, 2021 [1 favorite]
posted by Flock of Cynthiabirds at 9:48 AM on June 27, 2021 [1 favorite]
My most recent Excel comedy/horror story is a speadsheet of measurement data that is collected from the measurement devices in CSV format. The people responsible for the measurement devices thought it would be helpful to convert this to an excel spreadsheet before passing it along to the people that use the data - or maybe the recipients didn't want anything to do with this funny csv business and asked for an Excel file.
Now the original CSV file is usually one month of data, at 15 minute increments with a timestamp column in dd/MM/yyyy HH:mm format. Even though we're in the socialist states of Europe, Excel assumes the date column to be in MM/dd/yyyy format and happily messes the timestamp up for the first 12 days of the month - then realizes something is going wrong, switches to dd/MM/yyyy for the rest of the spreadsheet and hopes nobody noticed.
posted by each day we work at 9:48 AM on June 27, 2021 [6 favorites]
Now the original CSV file is usually one month of data, at 15 minute increments with a timestamp column in dd/MM/yyyy HH:mm format. Even though we're in the socialist states of Europe, Excel assumes the date column to be in MM/dd/yyyy format and happily messes the timestamp up for the first 12 days of the month - then realizes something is going wrong, switches to dd/MM/yyyy for the rest of the spreadsheet and hopes nobody noticed.
posted by each day we work at 9:48 AM on June 27, 2021 [6 favorites]
You really can't overstate the WOW! effect of spreadsheets on realtors. I set up a few for real estate offices and they just could not believe that you could punch in a few numbers (say, adjust the offered deposit) and the rest of the numbers would adjust to accommodate. It was like magic to them! (A dark magic, sometimes, but magic nonetheless.)
posted by SPrintF at 10:05 AM on June 27, 2021 [3 favorites]
posted by SPrintF at 10:05 AM on June 27, 2021 [3 favorites]
I feel like most of these issues happen when people expect whatever tool they're working with to magically do what they expect it to- and if it doesn't, it's clearly the tool's fault and not a lack of knowledge on their part. Which is a completely understandable viewpoint if the magic of "it just works!" has been imprinted on them at some point.
I've seen this with a team of very capable engineers. Give them a blank piece of paper (or blank excel sheet) and ask them to design something fairly straightforward and they will all do well. Give them a sloppily made calculation template for the same type of equipment, with formula errors, or obvious omissions, or hidden (non-checkable) functionality, and you get garbage. Somehow all critical thinking goes out the window, when "the spreadsheet did it" becomes a valid excuse. It's a pernicious sort of diffusion of responsibility that creeps in with all sorts of systems, not just excel.
posted by Jobst at 10:48 AM on June 27, 2021 [3 favorites]
I've seen this with a team of very capable engineers. Give them a blank piece of paper (or blank excel sheet) and ask them to design something fairly straightforward and they will all do well. Give them a sloppily made calculation template for the same type of equipment, with formula errors, or obvious omissions, or hidden (non-checkable) functionality, and you get garbage. Somehow all critical thinking goes out the window, when "the spreadsheet did it" becomes a valid excuse. It's a pernicious sort of diffusion of responsibility that creeps in with all sorts of systems, not just excel.
posted by Jobst at 10:48 AM on June 27, 2021 [3 favorites]
related to Jobst's comment above. I have been trying to get my design team to migrate away from excel for process calcs and return to a more linear top down "here are the equations and the arithmetic on numbered lines so each step can be checked", because the way we generally use excel for calcs does NOT lend itself to robust QA. (aside, I REALLY love smath as a tool but I can't seem to get anyone else to feel sufficiently dorked about it. I have debated about mandating one over the other but that's not really my style and so often leads to undesirable secondary consequences, not least that my team will think I am a prick)
posted by hearthpig at 10:54 AM on June 27, 2021 [1 favorite]
posted by hearthpig at 10:54 AM on June 27, 2021 [1 favorite]
The reason that Excel is in the wrong is because messing with formatting doesn't just change how data is displayed, it literally rewrites the data.
Everything you type into a spreadsheet will be interpreted as a number if at all possible, and that includes dates, times or anything that looks like it might be a date or time.
To enter something as a text string and prevent any interpretation by the program you can prefix the text with '
When you do this, you will notice the quote mark is automatically hidden from view, this is a standard text entry convention in every spreadsheet program since about 1983 and will take precedence over any other formatting applied.
posted by Lanark at 11:13 AM on June 27, 2021 [7 favorites]
Everything you type into a spreadsheet will be interpreted as a number if at all possible, and that includes dates, times or anything that looks like it might be a date or time.
To enter something as a text string and prevent any interpretation by the program you can prefix the text with '
When you do this, you will notice the quote mark is automatically hidden from view, this is a standard text entry convention in every spreadsheet program since about 1983 and will take precedence over any other formatting applied.
posted by Lanark at 11:13 AM on June 27, 2021 [7 favorites]
Flock of Cynthiabirds, I don’t think the 64K row/256 column limitation in xls files was bad programming, but a function of the computing restraints of the day. The xls format was introduced with Office 97, and typical computers of the day had nowhere near the memory we’re used to. 32-bit systems like Windows 9x could only address a total of 4GB of memory, and most consumer machines had 512MB or less. A spreadsheet had to fit into the space left over when Windows and Excel were running.
posted by lhauser at 11:43 AM on June 27, 2021
posted by lhauser at 11:43 AM on June 27, 2021
hearthpig: Anyone here heard of vpplanner? this was a 1-2-3 knockoff we used in first year uni computing classes in 1990 (on old monocrhome PC ATs) I have never come across anyone else who has heard of it.
I was a huge fan of VP Planner and VP Planner Plus. I even had a copy of the never released VP-3D, a la Boeing Calc. Used their stuff for 15 years or more. Gladly danced on Lotus' grave after the look-feel litigation nonsense. Funny thing was VP Planner was never meant to be a spreadsheet. It was built on a five dimensional matrix array. Slapping together a spreadsheet interface to get stuff in and out of it seemed to be the path of least resistance. Whenever I'm feeling nostalgic I'll go and spend some time with sc for laughs...
posted by jim in austin at 12:01 PM on June 27, 2021 [1 favorite]
I was a huge fan of VP Planner and VP Planner Plus. I even had a copy of the never released VP-3D, a la Boeing Calc. Used their stuff for 15 years or more. Gladly danced on Lotus' grave after the look-feel litigation nonsense. Funny thing was VP Planner was never meant to be a spreadsheet. It was built on a five dimensional matrix array. Slapping together a spreadsheet interface to get stuff in and out of it seemed to be the path of least resistance. Whenever I'm feeling nostalgic I'll go and spend some time with sc for laughs...
posted by jim in austin at 12:01 PM on June 27, 2021 [1 favorite]
I didn’t understand it, so I showed it to some members of Eusprig, the European Spreadsheet Risks Group. They spend their lives analysing what happens when spreadsheets go rogue.
Oh wow, can't think of a more boring group to be apart of.
posted by geoff. at 12:16 PM on June 27, 2021 [1 favorite]
Oh wow, can't think of a more boring group to be apart of.
posted by geoff. at 12:16 PM on June 27, 2021 [1 favorite]
I had the terrifying pleasure of several William Kahan lectures on how bad Excel is - eg, he found an algebraic expression with different results depending on logically-equivalent-according-to-Excel parentheses .
I had been a MSFT employee before taking that numerical course, but not in Excel. Enough to know that the Excel and Word teams leaned very hard on notfixing bugs changing behavior, no matter the damage done. There might be a customer depending on the idiosyncrasies! How convenient.
The NIST paragraph on why they don’t rate Excel for accuracy and reliability was a thing of art.
posted by clew at 12:30 PM on June 27, 2021 [3 favorites]
I had been a MSFT employee before taking that numerical course, but not in Excel. Enough to know that the Excel and Word teams leaned very hard on not
The NIST paragraph on why they don’t rate Excel for accuracy and reliability was a thing of art.
posted by clew at 12:30 PM on June 27, 2021 [3 favorites]
I have a friend who does set up Excel with checks: he creates a separate worksheet with formulas based on sentinel cells in other sheets that will change colour if expected values aren't produced. He has other tricks like this too. Basically, he's created assertions and unit tests for Excel. You can build robustness into spreadsheets, it's just that nobody (except my friend Keith) actually does.
posted by i_am_joe's_spleen at 12:45 PM on June 27, 2021 [10 favorites]
posted by i_am_joe's_spleen at 12:45 PM on June 27, 2021 [10 favorites]
If a user-Excel interaction keeps going wrong, you have to eventually ask if the problem is *really* the humans... or if Excel could be written better.
We're not going to get different humans, but we could rewrite the UI.
posted by Belostomatidae at 1:35 PM on June 27, 2021 [5 favorites]
We're not going to get different humans, but we could rewrite the UI.
posted by Belostomatidae at 1:35 PM on June 27, 2021 [5 favorites]
Oh wow, can't think of a more boring group to be apart of.
My grandfather founded that group. Thanks a lot.
posted by thelonius at 1:56 PM on June 27, 2021 [13 favorites]
My grandfather founded that group. Thanks a lot.
posted by thelonius at 1:56 PM on June 27, 2021 [13 favorites]
Debugging is fun.
Props to your Grandfather thelonius.
posted by Windopaene at 3:28 PM on June 27, 2021 [6 favorites]
Props to your Grandfather thelonius.
posted by Windopaene at 3:28 PM on June 27, 2021 [6 favorites]
I think one of the biggest problems is that people learn to use Excel, then fail to realize that some problems require a database instead of a spreadsheet. So you get abominations of mess where people attempt to make a spreadsheet do the work of a database and, at best, they kind of sort of vaguely get something like what they wanted but not quite.
posted by sotonohito at 3:37 PM on June 27, 2021 [4 favorites]
posted by sotonohito at 3:37 PM on June 27, 2021 [4 favorites]
I remember (from the early '70's) my CPA dad's books for a chain of chemists across southern England. Each month was a double page spread about 40 inches wide, a literal 'spread sheet' (there seemed to be special account books published for this).
I can also recall seeing him sum multiple columns of figures simultaneously in his head.
posted by unearthed at 5:07 PM on June 27, 2021 [1 favorite]
I can also recall seeing him sum multiple columns of figures simultaneously in his head.
posted by unearthed at 5:07 PM on June 27, 2021 [1 favorite]
I enjoyed this article about Microsoft Excel.
posted by latkes at 9:04 PM on June 27, 2021 [1 favorite]
posted by latkes at 9:04 PM on June 27, 2021 [1 favorite]
To enter something as a text string and prevent any interpretation by the program you can prefix the text with '
When you do this, you will notice the quote mark is automatically hidden from view, this is a standard text entry convention in every spreadsheet program since about 1983 and will take precedence over any other formatting applied.
Frustratingly enough, the quote is not part of the cell value and is not visible to stuff like substring or find and replace.
Typing something like 'HELLO' in a cell (or importing from another data source) can leave you with a cell containing (magic quote)HELLO' that will now look like 'HELLO' when you're editing the cell but evaluate and export as HELLO'. Much fun ensues when somebody double clicks on a csv or tab-delimited file , changes something then saves again subjecting all the cells to Excel data interpretation magic.
posted by each day we work at 10:27 PM on June 27, 2021 [5 favorites]
When you do this, you will notice the quote mark is automatically hidden from view, this is a standard text entry convention in every spreadsheet program since about 1983 and will take precedence over any other formatting applied.
Frustratingly enough, the quote is not part of the cell value and is not visible to stuff like substring or find and replace.
Typing something like 'HELLO' in a cell (or importing from another data source) can leave you with a cell containing (magic quote)HELLO' that will now look like 'HELLO' when you're editing the cell but evaluate and export as HELLO'. Much fun ensues when somebody double clicks on a csv or tab-delimited file , changes something then saves again subjecting all the cells to Excel data interpretation magic.
posted by each day we work at 10:27 PM on June 27, 2021 [5 favorites]
Every year (2020 and 2021 excluded due to pandemic) I have to divvy up season tickets for a local sports team. Everyone else always complained about dividing them up using old school methods. When I took over the seats as the main guy, I thought I'll make this easy and use excel! Now I get to complain about dividing them up because it's still not easy, AND about how my excel spreadsheet is broken in a unique way. I do though verify the outputs for number of weekends vs weekdays etc. But yeah it makes some things easier but it's not a magic bullet.
posted by Carillon at 12:09 AM on June 28, 2021 [1 favorite]
posted by Carillon at 12:09 AM on June 28, 2021 [1 favorite]
unearthed, the linked article mentions that Dan Bricklin's original inspiration was accounting ledgers, then also known as spreadsheets. Other people have become billionaires with less influential insights than Bricklin's.
posted by epo at 2:18 AM on June 28, 2021 [1 favorite]
posted by epo at 2:18 AM on June 28, 2021 [1 favorite]
After years of using spreadsheets, learning SQL and RDBMS was a game changer. When I see someone in my work using a cobbled together Excel with linked sheets (or so-called “excel wizards”) I don’t know whether to be an evangelist or just shake my head. Learning databases and minimal scripting language and libraries (python, pandas) should be a requirement for anyone working with data.
posted by iamck at 8:25 AM on June 28, 2021 [1 favorite]
posted by iamck at 8:25 AM on June 28, 2021 [1 favorite]
Back in my helpdesk days (1990's) - I had a user come to the desk with questions, at a University. There were having trouble formatting their resume, and it was visible obvious from the print out. I figured this would be a case where I would show them the magic of using the ruler in Word and be done.
nope, the whole resume was in Excel and was using columns and rows, etc. to enter the resume and then trying to format the text. 10 points for patience, but tunnel vision on the tool was happening.
I still believe a "Intro to the Philosphy of Spreadsheets" is a needed so people understand the power, and the limitations.
posted by fluffycreature at 9:55 AM on June 28, 2021 [1 favorite]
nope, the whole resume was in Excel and was using columns and rows, etc. to enter the resume and then trying to format the text. 10 points for patience, but tunnel vision on the tool was happening.
I still believe a "Intro to the Philosphy of Spreadsheets" is a needed so people understand the power, and the limitations.
posted by fluffycreature at 9:55 AM on June 28, 2021 [1 favorite]
After years of using spreadsheets, learning SQL and RDBMS was a game changer. When I see someone in my work using a cobbled together Excel with linked sheets (or so-called “excel wizards”) I don’t know whether to be an evangelist or just shake my head. Learning databases and minimal scripting language and libraries (python, pandas) should be a requirement for anyone working with data.
I don't disagree but let's not overlook those poor souls who are stuck in spreadsheets because nothing else is allowed on their work computers. I was one of them in a former life and, let me tell you, I built some beautiful monsters that never should have been.
posted by treepour at 12:34 PM on June 28, 2021 [3 favorites]
I don't disagree but let's not overlook those poor souls who are stuck in spreadsheets because nothing else is allowed on their work computers. I was one of them in a former life and, let me tell you, I built some beautiful monsters that never should have been.
posted by treepour at 12:34 PM on June 28, 2021 [3 favorites]
if you spend 100 florins on wool, that is recorded as a credit of 100 florins in your cash account and a debit of 100 florins worth of wool in your assets account.
Is this a mistake?
No, no mistake. You may be thinking of credit as being an increase and debit being a decrease, like you see in your bank account statement; but that's because bank account statements are prepared from the bank's point of view so when they show it to you, it's "the wrong way round".
For example, liabilities (in double-entry book-keeping) are recorded as credits; so if you have £500 saved in your bank account, that's a liability on the bank's accounts and entered by them as a credit in their accounting system. So when the bank just prints off their ledger for your account - which is what your bank statement is - it says £500 credit.
But if you're a business who has £500 in the bank, that's an asset to you so you record it in your own accounts as a debit.
Yeah, it's confusing :(
posted by vincebowdren at 1:27 PM on June 28, 2021 [2 favorites]
Is this a mistake?
No, no mistake. You may be thinking of credit as being an increase and debit being a decrease, like you see in your bank account statement; but that's because bank account statements are prepared from the bank's point of view so when they show it to you, it's "the wrong way round".
For example, liabilities (in double-entry book-keeping) are recorded as credits; so if you have £500 saved in your bank account, that's a liability on the bank's accounts and entered by them as a credit in their accounting system. So when the bank just prints off their ledger for your account - which is what your bank statement is - it says £500 credit.
But if you're a business who has £500 in the bank, that's an asset to you so you record it in your own accounts as a debit.
Yeah, it's confusing :(
posted by vincebowdren at 1:27 PM on June 28, 2021 [2 favorites]
I still believe a "Intro to the Philosphy of Spreadsheets" is a needed so people understand the power, and the limitations.
I blame high school computing classes that teach "Word" or "Excel" rather than teaching what a word processor is, what a spreadsheet is, or what a database is (the lack of approachable for an end user to set up databases these days makes that a bit harder, of course).
posted by eviemath at 1:31 PM on June 28, 2021 [2 favorites]
I blame high school computing classes that teach "Word" or "Excel" rather than teaching what a word processor is, what a spreadsheet is, or what a database is (the lack of approachable for an end user to set up databases these days makes that a bit harder, of course).
posted by eviemath at 1:31 PM on June 28, 2021 [2 favorites]
the lack of approachable for an end user to set up databases these days makes that a bit harder, of course
Airtable.
posted by iamck at 3:37 PM on June 28, 2021
Airtable.
posted by iamck at 3:37 PM on June 28, 2021
I dunno, Access really is fairly straightforward as far as databasing goes. I'm not a huge fan, it's got weird limits, but it works and it's a lot more user friendly than tossing someone out and saying "here write an SQL command to create all the tables you need".
The problem isn't that there's not an approachable database application, the problem is that relational databases are something that takes learning how to think at least slightly differently and that's a lot of work.
Programming is one of those areas where learning how to do it irrevocably changes the way you think. Once you learn how to think like a programmer you can never stop thinking like a programmer.
I understand really learning drawing or music is similar. Or for a smaller example: learning how to drive.
Once you know how to drive, really seriously know to the extent where you can drive without thinking about it, you will never just be a passenger again. That part of your brain you trained to be a driver is always thinking like a driver.
Databases are much the same, but related enough to programming thought that if you know how to program databases often seem obvious. How could anyone **NOT** grok why you need a join table?
To a person going in with no programming background the whole thing is weird and opaque and counterintuitive and just plain confusing.
How do I know? Easy: I taught an intro to database class way back when, and at first it was amazing to me how difficult people found the concepts. Just the need for multiple tables seemed really confusing to them, the very concept of one to many relations seemed hard even with lots of analogies and examples, and many to many was just... yeah.
Which brings me back to my point. The problem isn't inadequate database software. It's that the very concept requires a significant change in thinking and that takes a lot of work and more time to develop than employers like to think about.
posted by sotonohito at 8:12 AM on June 29, 2021 [1 favorite]
The problem isn't that there's not an approachable database application, the problem is that relational databases are something that takes learning how to think at least slightly differently and that's a lot of work.
Programming is one of those areas where learning how to do it irrevocably changes the way you think. Once you learn how to think like a programmer you can never stop thinking like a programmer.
I understand really learning drawing or music is similar. Or for a smaller example: learning how to drive.
Once you know how to drive, really seriously know to the extent where you can drive without thinking about it, you will never just be a passenger again. That part of your brain you trained to be a driver is always thinking like a driver.
Databases are much the same, but related enough to programming thought that if you know how to program databases often seem obvious. How could anyone **NOT** grok why you need a join table?
To a person going in with no programming background the whole thing is weird and opaque and counterintuitive and just plain confusing.
How do I know? Easy: I taught an intro to database class way back when, and at first it was amazing to me how difficult people found the concepts. Just the need for multiple tables seemed really confusing to them, the very concept of one to many relations seemed hard even with lots of analogies and examples, and many to many was just... yeah.
Which brings me back to my point. The problem isn't inadequate database software. It's that the very concept requires a significant change in thinking and that takes a lot of work and more time to develop than employers like to think about.
posted by sotonohito at 8:12 AM on June 29, 2021 [1 favorite]
In a normally debited account, such as an asset account or an expense account, a debit increases the total quantity of money or financial value, and a credit decreases the amount or value. On the other hand, for an account that is normally credited, such as a liability account or a revenue account, credits increase the account's value and debits decrease that value.
This seems confusing to me, but it does explain some terminology I didn’t know. Nor do I doubt the sharpness of the tools of 300 years of Civ & Cap. Huh.
A friend of mine is a DB programmer and a bookkeeper, in separate pursuits, I shall ask him which changed his worldview more.
posted by clew at 9:02 AM on June 29, 2021
This seems confusing to me, but it does explain some terminology I didn’t know. Nor do I doubt the sharpness of the tools of 300 years of Civ & Cap. Huh.
A friend of mine is a DB programmer and a bookkeeper, in separate pursuits, I shall ask him which changed his worldview more.
posted by clew at 9:02 AM on June 29, 2021
... some crufty excel spreadsheet that's been incrementally updated for over a decade by a string of workers who are all terrified to touch it because of apocryphal tales about that time someone screwed it up and they had to rebuild ...
3 - 4 years ago I saved our local cat shelter's severely munged Excel "database" of cats by OCRing the most recent hardcopy and doing a lot of hand-massaging of data and formatting.
To this day the active filename is still "Reconstructed Shelter Database.xls"
posted by ZenMasterThis at 7:17 PM on July 2, 2021 [2 favorites]
3 - 4 years ago I saved our local cat shelter's severely munged Excel "database" of cats by OCRing the most recent hardcopy and doing a lot of hand-massaging of data and formatting.
To this day the active filename is still "Reconstructed Shelter Database.xls"
posted by ZenMasterThis at 7:17 PM on July 2, 2021 [2 favorites]
« Older Novels as a kind of literary seismograph | "The world is full of love, and you’re entitled to... Newer »
This thread has been archived and is closed to new comments
Is this a mistake? I would think that if you *spend* 100 florins *acquiring* wool, then your asset ledger would reflect a credit for "+ wool (x units) - 100 florins" and your cash account would reflect a debit "- 100 florins (woo, x units)", but IANAA.
posted by wildblueyonder at 11:10 PM on June 26, 2021