Why You Shouldn't Use Spreadsheets For Important Work

An anonymous reader writes "Computer science professor Daniel Lemire explains why spreadsheets shouldn't be used for important work, especially where dedicated software could do a better job. His post comes in response to evaluations of a new economics tome by Thomas Piketty, a book that is likely to be influential for years to come. Lemire writes, 'Unfortunately, like too many people, Piketty used spreadsheets instead of writing sane software. On the plus side, he published his code ... on the negative side, it appears that Piketty's code contains mistakes, fudging and other problems. ... Simply put, spreadsheets are good for quick and dirty work, but they are not designed for serious and reliable work. ... Spreadsheets make code review difficult. The code is hidden away in dozens if not hundreds of little cells If you are not reviewing your code carefully and if you make it difficult for others to review it, how do expect it to be reliable?'"

Comments

What he's really saying is

[Anonymous Coward]

"I don't know how to use spread sheets properly."

Re:What he's really saying is

[Anonymous Coward]

To be fair, neither to the vast majority of people who use spreadsheets for important work.

Re:What he's really saying is

[Anonymous Coward]

Disagree. I think what he's really saying is "I've had to maintain and develop tools made by people that don't know how to use spreadsheets properly, and I'm fucking sick of it."

Re:What he's really saying is

[Tyler Durden]

I know exactly how to use spreadsheets properly. Just don't.

Re:What he's really saying is

[AK Marc]

I thought it was getting an award for being the 10,000,000th restating of GIGO.

Re:What he's really saying is

[geekoid]

It needs restating because people forget it all the time.

Re: What he's really saying is

[Anonymous Coward]

Most people have no idea how to use a relational database.

Re:What he's really saying is

[jd2112]

"I don't know how to use spread sheets properly."

Or, I realize that just because I have a hammer not all problems are nails.

Re:What he's really saying is

[jythie]

Eh, I think it can be legitimately argued that spreadsheets are a bad place to do complex things. Even people who are skilled at setting them up produce work that is difficult to examine and track. In many ways it is a technology that it still stuck in the 80s, even though they keep throwing in more and more complex functionality, but the method of storing and organizing the logic is dated in a bad (rather then proven) way.

Even teaching students matlab would probably be an improvement, but excel is what they default to teaching anyone outside math and CS, building all the coursework around it.

Re:What he's really saying is

[Giant Electronic Bra]

No kidding. Also, it MAY not be that easy to review the code in a spreadsheet, but it is VERY VERY EASY to test it. If you want reliable spreadsheets its PERFECTLY possible to test them to the Nth degree, far more so than with most other code. You have a place to put the tests, and a place to put the expected results, its all rather devilishly simple actually. For that matter you can document the bejeezus out of them too.

I think spreadsheets are like any sort of simple interpreted language. Idiots can easily blow their left foot off. Real software engineers can also do some very cool stuff. Most of the perl code I've seen is ugly as all hell and pretty worthless, but MY perl code is a thing of beauty that people maintain for years. Its all in how you use the tool.

Re:What he's really saying is

[jythie]

To be fair, they have their place. They are excellent tools for creating a table of data with a chart that can be emailed to other people and read.

Sadly, one of the big selling points for spreadsheets is their application. Pretty much any computer being used for work will have something that can read and display excel spreadsheets, you can send one to anyone and not have to worry about what they have installed. Then again you can get the same level of compatibility by outputting PDFs from matlab or something slightly saner like that....

Re: What he's really saying is

[wezelboy]

Mod parent up.

Re:What he's really saying is

[Ol Olsoc]

To be fair, they have their place. They are excellent tools for creating a table of data with a chart that can be emailed to other people and read. .

What happens though is they become the only tool in the toolbox. Had a junior in accounting one time to insist on what should have been a PowerPoint program made up in Excel. Figuring that experience was something you get right after you needed it, I did it as demanded, and told her I'd see her when she came back to ask me to do it in the proper program. She was a bit of a snot until she had to come crawling back with a "You were right - they crucified me during the review".

Re: What he's really saying is

[Ol Olsoc]

Just got back from a meeting where I was explaining relational databases. No one really got it, but the Excel expert thought I was full of shit.

Well, I sort of am, but not for that reason.

There is nothing worse than trying to get a spreadsheet person up and running on relational databases. They argue with you about every point, then they freak.

Re:What he's really saying is

[Darinbob]

No, he's saying that it's extremely difficult to tell of other people are using spread sheets properly. It's easy to make a mistake in them that no one ever discovers. Then that broken spread sheet gets passed around, treated as the all important proof about some topic. If this is about science, then the rule to show your work is critical whereas a spread sheet allows much of the work to be hidden.

Even a quick and dirty spreadsheet used to make a point often gets taken and spread around and used as they key justification in a new business plan, for example, with no one ever examining each and every cell to be sure it's correct. As opposed to for example sending out an email or paper with the annotated formulas up front and visible with the spreadsheet as merely an addendum if someone wants to try some variant. I have seen spread sheets saved as a major part of the documentation for a project without ever having any description about how those numbers were calculated.

So how do you code review a spread sheet, and have you ever seen it done?

If something is important enough to base a major journal paper on, or a student's thesis, then it's necessary to have a program to churn the data rather than a spreadsheet.

Re: What he's really saying is

[Anonymous Coward]

Or OpenOffice or LibreOffice or Symphony or about a dozen more applications for office work including apps for Android and iPhone...

Re:What he's really saying is

[Darinbob]

Except that you must look at each and every cell to know what's in them. A cell may look like just a piece of raw data, but it might be a formula underneath. It's really easy to lie with a spread sheet too, just document the bejeezus out of it and everyone will read that documentation without looking at the formulas to see what you're really doing. It's harder to do that even with Perl because someone's going to scan over the code, so you need to be a lot more subtle to lie with a program.

Re:What he's really saying is

[Darinbob]

Oh, and by "lie", this also includes making a simple mistake that you don't notice.

Re: What he's really saying is

[NatasRevol]

Unless the spreadsheet has Excel macros running.

Re:What he's really saying is

[ClickOnThis]

you can send one to anyone and not have to worry about what they have installed

Except that they need to be running Windows or Mac, with Microsoft Office installed.

Actually, LibreOffice/OpenOffice are pretty good at importing and exporting .xls and .xlsx. And considering how incredibly obfuscated^H^H^H^H^H^H^H^H^H^H complicated the MS OOXML standard is, I'd say that's quite an accomplishment.

You can even import .ods in MS Excel, if you have the relevant plugins installed.

That said, I agree with TFA: don't go overboard with fancy spreadsheets. Keep them simple, for the sake of your own mental health and that of your co-workers.

Re:What he's really saying is

[formfeed]

"I don't know how to use spread sheets properly."

No. He doesn't:

I will happily use a spreadsheet to compute the grades of my students, to estimate my retirement savings, to compute how much tax I paid last year but I will not use Microsoft Excel to run a bank or to compute the trajectory of the space shuttle.

What he is saying is: It is fine to use spreadsheets as spreadsheets.
But there are people who can't use statistical analysis tools who use spreadsheets instead.
People who can't program who use spreadsheets filled with little code snippets to do what a program should do.
And in general people who make a mess with spreadsheets getting results that are hard to audit.

In short, business majors should take statistics classes and learn mathlab, or opt out of it by signing a form promising never to publish any paper that deals with statistical analysis of market factors.

Re:What he's really saying is

[qwak23]

This happens all the time where I work. A coworker of mine recently made a lateral transfer and inherited a spreadsheet that was being used to track all of the personnel in the department (over 200 people). At first he was just going to roll with it, until other users of the spreadsheet kept breaking things (the guy who he inherited it from used to just play clean up after everyone else). Now we have a database because you know, proper tool and all, user control, etc

Re:What he's really saying is

[radarskiy]

"Laymen should not be allowed to read the Bible independently."

Re:What he's really saying is

[aaronb1138]

An interesting remedy would be a "code view" mode for spreadsheets where calculations were displayed as nested operations and such. It would require a stronger intent manager that could recognize the same sequence of code running on rows 4-53 until column N, but it could work. Sure, this mostly sounds like a database, but instead give it the modularity and ease of current spreadsheets and everything works out.

Re:What he's really saying is

[Coeurderoy]

No, what he is saying is that it is easy to "write" sloppy code for excel and hard to write good code.
And even harder to review it.

It's similar to the reason a) people moved away from basic, and b) basic evolved to be (duck, please no flame) almost usable (I still do not like it, but recognize that it is possible to write usable code in visual basic).

If you want to criticizes him, picking on Piketty is VERY political, "excel" errors are galore in neocon publications, but of course the FT did not find anything not to love there, but saying that just maybe having a small group of people siphoning off all the cash from society is not sustainable for ever does make them nervous and very desirous to find some scab to pick at...

Nevertheless he is right, it would be very good if decision makers would be able to "read the numbers" and not just "massage the numbers".
Something like R or ADaMSoft would drive you to test ideas on datasets and learn from them whereas excel (or calc :)) have a tendency to get you to fiddle the numbers until the taxman aherm the reader sees what you would like them to see...

Re:What he's really saying is

[mgf64]

To be fair, neither to the vast majority of people who use spreadsheets for important work.

Most economists don't. This is not the first case.

Re:What he's really saying is

[RabidReindeer]

Garbage In, Gospel Out

Re:What he's really saying is

[RabidReindeer]

One of the nastiest things about spreadheets in relation to software is that software is essentially linear, making it easier to follow what's affecting what.

Spreadsheets are 2-dimensional, can incorporate data from invisible cells and even other sheets. Then on top of that, what's normally displayed is the results, not the code. There's no side-by-side view of code/value on any spreadsheet I'm aware of.

Also, since code is linear, one screwup and it tends to make itself obvious by propagating downstream. Spreadsheets can have pockets of decay intermingled with perfectly good stuff.

Re:What he's really saying is

[Anonymous Coward]

I think you nailed it.

Re:What he's really saying is

[rtb61]

Spreadsheets are really easy to use properly, all you have to do is adjust your mind to the idea of creating two styles of spread sheet, the working spread sheet, well laid and and documented, to ensure the workings are understandable and checked and a linked presentation spreadsheets where the data is taken from the working spreadsheets and presented prettily of nepotistic management, so even the dumbest spawn of management can, well, at least pretend to understand.

Other things you can do is check formulas, where the totals and calculations are done in different manners and taken from different sources within the spreadsheet in order to check for errors, when the outcomes do not match.

The real problem with spreadsheets is they are completely and totally unable to present in simple terms what years of experience have taught and in the boardroom competition of what data gets accepted and rejected. The easy high profit (often completely and totally unrealistic) are accepted, against the words of wisdom drawn from experience, simply because it can not be presented in a spreadsheet ie reducing customer support and outsourcing 90% will save 90% of costs, the spreadsheet vs cutting support and making it shite will piss off lots of customers often for life, experience, impossible to accurately express in a spreadsheet of course as it is extremely complex and subject to product quality, clearly explained sales and what competitors are up to.

Re:What he's really saying is

[Neil Boekend]

In MS Excel 2003 and 2013 ctrl-T is your friend when debugging a couple of thousands of cells with formulas. It also indicates what cells have formulas and what cells have values.

Having said that, I am busy trying to convince my boss to have a massive pain in the ass excel working document with dozens of macros and a thousands of rows converted to a relational database.
Excel is useful, but people often exceed it's limits.

Re:What he's really saying is

[Neil Boekend]

I have done code review on spreadsheets, even when showing formulas with ctrl-t it sucks. But it can be done.

Re: What he's really saying is

[meglon]

There is nothing worse than trying to get a spreadsheet person up and running on relational databases. They argue with you about every point, then they freak.

Sure there is... there's trying to get a db user to understand spreadsheets. How many times have i told you, the right tool for the right job (watch out for that low hanging pipe! ..... nevermind....).

Re:What he's really saying is

[meglon]

.... but some are.

Re:What he's really saying is

[Boronx]

This is all true, but you're really just saying that the current state of spreadsheet interface is poor. A spreadsheet ought to be easier to trace input and outputs than code. And there's no reason there shouldn't be views of spreadsheets that lay the programming out for you. If these problems were solved, then spreadsheets would blow away code for readability in a large class of applications.

Re:What he's really saying is

[CadentOrange]

But spreadsheets are the right tool for works that require fudging. It makes it that much harder for a neutral 3rd party to verify the correctness of your algorithms.

Re:What he's really saying is

[Cytotoxic]

I would support this approach. Excel is great for business users to prototype calculations and play around with data. When it is time to put what they have learned into practice, ship it off to software engineers and let them turn it into a more stable product for use by the less sophisticated user.

Of course, reverse engineering a complicated excel workbook can be a royal pain in the rear. This is where you learn just how devilish a tool excel can be, with hidden rounding and other obscure effects.

Re:What he's really saying is

[Giant Electronic Bra]

LOOKING AT the code is not testing it, 90% of all issues won't show up when you look at a piece of code, unless you're so thorough that you might as well have written tests (which is always a better way). Spread sheets are IDEALLY testable, each cell has defined inputs and outputs and you have a built-in way to enter data into it. You can also build another spreadsheet of expected outputs (heck, maybe that's just a cut and paste of the values you got the first time you used the thing, but at least that lets you test regression). Once you have expected outputs you can check them AUTOMATICALLY with a third sheet (IE difference the actual vs the expected, you should get all zeros). This kind of thing is trivial.

Sheesh, the problem here is people are LAZY. They want things to just be correct magically without any work. I got news for you, it ain't ever so. My first job was validating that a critical part of the flight control system of the 747-400 actually worked as advertised under all circumstances. You think we LOOKED AT THE CODE???!!! lol. Likewise, if you're going to make very expensive business and economic decisions then you FRIGGING SPEND THE TIME TO TEST, and once you decide you're going to do that, spread sheets are eminently testable.

Re:What he's really saying is

[Giant Electronic Bra]

Well, I agree that spread sheets can be pretty obscure and there's a point where they aren't the best solution, perhaps. Of course if you do it right you can migrate a lot of the logic to a backend database or into code modules that still provide inputs to the spread sheet. Sheets are great for presentation and organization of certain types of numerical data, and with the built-in charting features they can be pretty good general data visualization tools. You just have to understand at what point to offload onto some other tool at least part of the work.

Re:What he's really saying is

[cyberthanasis12]

Sure. But the same is true about GOTOs. You CAN write reliable code using GOTOs, if you have self discipline. But in practice 99% of code with GOTOs is abused. Hence the idiom "GOTO is evil".

Spread sheet is evil.

Re:What he's really saying is

[hairyfeet]

Perfect is the enemy of good. If your workflow benefits from spreadsheets? Then just ignore the jerk in TFA which frankly smacks of elitist BS. Why the fuck should my customer NEED to use a dedicated program for each task if the work gets done fast and correct with a spreadsheet?

Are there jobs where a dedicated is better? YES, for example I would be hard pressed to find anything better for a small business owner than Quickbooks, you can run everything from payroll to taxes with just a QB girl (and for some reason its ALWAYS a girl, you'd think they had a union) and the place will run like a Swiss watch but just because ONE job is better with a dedicated does NOT mean that ALL jobs are better and for every one like the QB example there are probably a dozen where a spreadsheet can be whipped off and fill the roll just fine.

Re: What he's really saying is

[NatasRevol]

If the enduser is using Excel macros, they should have wrote something in C using Emacs or vi.

You're a fucking tool.

Re:What he's really saying is

[Gr8Apes]

Not for anything that is repeated over time. I've seen countless spreadsheet "managed" items and the errors that creep in over the months and years is pretty hilarious | scary | useful (for cons) etc. I wouldn't be surprised if a large part of the .com bubble was due to spreadsheet "math".

Re:What he's really saying is

[idontgno]

Not for anything that is repeated over time. I've seen countless spreadsheet "managed" items and the errors that creep in over the months and years is pretty hilarious | scary | useful (for cons) etc

Sounds like the classic definition of "legacy software".

You know, custom software written so bright and new and error-free a dozen years ago but now is so bug-ridden and completely incomprehensible that no one can maintain it? You've heard of it, right?

I've lived it. Trust me, there's nothing inherently worse about a spreadsheet compared to a 150,000 card COBOL deck.

In fact, TFA's hysterics completely miss the point. The tool isn't the problem: it's the skill and care of the person using the tool. A few thousands lines of FORTRAN written by a PhD meteorologist or a nuclear physicist prove that pretty clearly. (I've had to maintain those, too.)

A well-written, well-reviewed spreadsheet is better than a sloppily-written program written in (what's the flavor of the week, Python?) Python.

Re:What he's really saying is

[LordLimecat]

Clearly the solution is to have those people write custom code to do the job rather than using spread sheets.

Re:What he's really saying is

[Will.Woodhull]

Spreadsheets are definitely valuable in analysis and design phases. That can sometimes be very complex work, especially before all the constraints and contingencies of the project are identified. But as a general rule, even simple spreadsheets have no place in production work.

A spreadsheet is a great way to test the usefulness of different ways of handling complex data. They are also relatively easy to modify as a project's specifications evolve. Which they always do.

However an important part of implementing a project-- moving it from design into production-- is building software that is more robust than spreadsheets can be for use in the daily grind. That will sometimes be a database, but even a sequence of Perl or Python scripts is more robust over time than any spreadsheet. You have no control over the skills and knowledge of some temp whose been hired to fill in for that admin assistant who is in hospital for an appendectomy. That temp needs to be working in a highly constrained environment where he cannot do any unintentional damage, and no spreadsheet can supply that kind of environment.

Re:What he's really saying is

[david672orford]

Perfect is the enemy of good. If your workflow benefits from spreadsheets? Then just ignore the jerk in TFA which frankly smacks of elitist BS. Why the fuck should my customer NEED to use a dedicated program for each task if the work gets done fast and correct with a spreadsheet?

He doesn't say you shouldn't use spreadsheets at all. He acknowledges that they are fine for the tasks for which they were designed such as computing overall grades. What bothers him is the use of large, complicated, difficult to audit spreadsheets to make important decisions. They are difficult to audit because generally all you see is the input and the output. To see the "code" you have to poke around.

Instead he probably wants researchers to put the data into files and write small batch programs to process it and spit out results. You do not need to be a programming wiz to write programs like that. Certainly anyone who can create a large spreadsheet with formulas can learn to do it.

This approach has huge advantages. One is that humans can easily see the program and understand what it does. (If they cannot, then the results are not to be trusted.) You can also create test files containing amounts of data small enough that a human can grasp it and know what the correct result should be.

So no, this is not elitist. He is saying that if you intend to publish your results or have others verify them you should not use opaque tools just because you already know how to use them. If you can't to learn to use simple appropriate tools, why are you doing it at all?

Re:What he's really saying is

[lonecrow]

Spreadsheets are just a part of the Darwinism of applications. Some sharp fellow within an organization things its important to start tracking some data point or another. Maybe it gets ignored and forgotten. Other times it grows as other people see its utility and start making requests to track related data points. Eventually you get a multi-worksheet or even multi-workbook spreadsheet masquerading as an application. At some point it becomes far to hard to maintain or understand so they contract out someone like me who moves it to a relational database with a web front end. Everyone is happy!

This work forms a major part of my work load don't fuck with it!

Also, it is appropriate. It would be inefficient to develop a proper relational database application on the whim that some set of data points might be useful. Spreadsheets are a proving ground, and important stage in the life cycle of an application.

Re: What he's really saying is

[Ol Olsoc]

There is nothing worse than trying to get a spreadsheet person up and running on relational databases. They argue with you about every point, then they freak.

Sure there is... there's trying to get a db user to understand spreadsheets. How many times have i told you, the right tool for the right job (watch out for that low hanging pipe! ..... nevermind....).

Absolutely. Which is why for my present tasks, I bring in a slew of textfiles as CSV into a spreadsheet, then get rid of some trash in them, then export the results into a database changing the order and the fields used as needed. Other times simple counts and shuffling and finds are used. I probably spend 30 percent of my time in the spreadsheet.

But seriously, I've had a lot more trouble getting Spreadsheet users onto databases, than the other way around.

Re:What he's really saying is

[Douglas Goodall]

Spreadsheets are great for managing moderate two dimensional tables of data. I sometimes do my monthly budget on one. TFA is about the difference between vertical and horizontal applications. For people that can visualize, the spreadsheet is a powerful tool for managing moderate tables of data, such as my personal monthly budget. But I wouldn't try to use it for my complete yearly financial activity. Vertical applications are for people that benefit from pre-written software whose design embodies the details that help users. You can use spreadsheets for taxes, but knowledge apps for tax prep do a great job.

Re: What he's really saying is

[alva_edison]

using Emacs or vi.

Don't Mode Me In

Re: What he's really saying is

[I'm New Around Here]

Ha ha! Now that's a funny response. :^)

Re:What he's really saying is

[hairyfeet]

And that is different from any other long term software....how exactly? I've had to build NOS systems for customers whose ancient software wouldn't run on anything newer than an IDE drive loaded with with Win2K (XP would run it but it was buggy as 3 day old shit in the woods) and seen dedicated software that you couldn't even INSTALL on a modern OS because it had to be tied with some lousy old version of Flash or Java.

So nothing you have said makes dedicated a better choice over spreadsheets, on the contrary I've never seen an old sheet that wouldn't run on a new OS while I've seen plenty of dedicated where you'd need an old box lying around just to fire it up.

Re: What he's really saying is

[david_thornley]

Yup. Relational databases aren't how most people think. They're easily understandable if you've got a good grounding in first-order predicate logic, but most people are rather fuzzy on that. Spreadsheets model how most people think much better.

Re:What he's really saying is

[MisterToad]

We need to remember our history. First there was VisiCalc, then Lotus, etc. These were used by end-users who didn't want to put up with I.T. professionals. So all the software engineering and quality was thrown out. Then upper management gave these end-users the budget!!

Re:What he's really saying is

[plover]

The definition of legacy software is quite simple: legacy software is code for which you do not have automated tests.

That's it. Automate your testing and you can change anything you like, and still know the software works as designed. Refactor, rearrange, chop out blocks of dead code, whatever, as long as it still passes the tests.

Of course your tests have to be readable, and comprehensive, and trustworthy, but if you've gotten that far, you're golden.

Re:What he's really saying is

[MercTech]

And so often, you can do things with "it's only a spreadsheet" that the IT departments will foam at the mouth and scream bloody murder if you actually write some code over.

I've seen a person fired for writing a simple BAT file to automate data transference and storage.

Re:What he's really saying is

[Gr8Apes]

Running buggy crap on a new OS isn't any better than running dedicated legacy on old hardware/software, IMNSHO. Having rewritten more than one system to migrate from 10+ year old legacy code into something approaching the modern world will make you appreciate modern tools a lot.

But you're right in one thing - I haven't explicitly nailed the reason spreadsheets are terrible - hard links vs relative links, no error checking, link across multiple pages, functions spanning multiple rows/columns/pages for values that seemingly are unrelated, all because last week/month/year/decade that particular page had a number that made sense that particular day but no one since remembers why. the list goes on - spreadsheets are entirely unstructured and unconstrained when it comes to creating "code". That fudge factor that "Joe" threw in for an estimate never got removed, now your investor reported numbers are all skewed by it, but no one knows. etc etc etc.

I guess that's way more than one reason. At least with real code, things are usually more deterministic and easier to unravel.

Re:What he's really saying is

[ananthap]

Agreed 99% (except for your use of the word masquerading). I would prefer "forced to substitute for a proper application.. " or some such language. OK

Re:What he's really saying is

[cwsumner]

Quickbooks is not a dedicated program, and it can not be code-reviewed, either.

He is talking about scientific programs that need to be reviewed by many other people, to a level that can not be done with off-the-shelf tools.

That type of progrm is often written in Fortran, mosly because it uses libraries that have been reviewed before and that reduces the work.

Re:What he's really saying is

[cwsumner]

Clearly the solution is to have those people write custom code to do the job rather than using spread sheets.

Ha ha! Now that's a funny response. :^)

However, it is the correct one. He is not talking about the work you do...

Re:What he's really saying is

[jwhitener]

At some point it becomes far to hard to maintain or understand so they contract out someone like me who moves it to a relational database with a web front end. Everyone is happy!

This work forms a major part of my work load don't fuck with it!

This still happens a lot? Holy cow.... I was doing that 16 years ago out of college. I would have assumed that the business world would be turning out new graduates that realize that spreadsheets are not databases... guess not.

Well good for you then. Pretty easy (technically) work, and people really are super happy to see their spreadsheets turned into websites.

Re:What he's really saying is

[plover]

Because each cell is independently calculated, the problem with that is in visualizing it the same way you would a massively parallel program, one with crazy amounts of interdependencies, and that would be hard to represent and understand. Yes, I know a spreadsheet is not truly parallel, and that there is an order of computation, but when you look at it as a single linear task, it's like the mother of all oversized functions - which is to say it's also unreadable.

Individual areas of computation need to be modularized and encapsulated - both readable and testable. This can be done in a spreadsheet by using careful structure and organization, but it's not the default. And as most spreadsheets organically grow as new requirements or analysis is performed, it's not apparent when in the modification process such organization should be applied.

Ultimately, it requires the same skills as good software design in any system or language. And there is no requirement that a spreadsheet creator has to have good software design skills.

Spreadsheets - best and worst thing there is

[BeerCat]

Spreadsheets are like a blank piece of paper with grid squares. Which means you can put anything down, tied together with some formulae, and it's brilliant.

Which is also why it's complete pants - the "anything goes" really does mean that.

(That, and it will tend to break when you most rely on it)

Re:Spreadsheets - best and worst thing there is

[Anonymous Coward]

My boss uses spreadsheets for everything, because it's what he knows. I cannot tell you how many times I've been called in to clean up his messes because he's trying to do something in a spreadsheet that really should have been done with a database.

Re:Spreadsheets - best and worst thing there is

[plover]

What people fail to realize is that spreadsheets are like any other form of programming, and therefore should be treated as such. Write tests. Break complex formulas down into named cells. Use references to carry concepts. Beware of globals. Keep small concepts small, simple, and modular. Write more tests.

Does anybody do that with every spreadsheet they write? Doubtful. I know I only go to all that trouble myself when I have a boatload of inputs that have to get put together. I usually discover about part way in that the sheet is going to be complex enough to need tests. When I do, it's time to start refactoring it, and these are my general steps:

1. Give cells and ranges meaningful names
2. Break complex formulas down to several small formulas
3. Add tests for the formulas
4. Factor out duplicates

Of all of these, giving cells and ranges names is the most important, because it makes the sheets readable. I can then usually understand the results well enough to know if my formulas are working, but a complex formula often needs an independent set of tests to prove the discontinuities in the functions are actually where I think they should be.

Re:Spreadsheets - best and worst thing there is

[Bonobo_Unknown]

The problem isn't that people that know how to program and to test are writing crappy spreadsheets. The problem is that people that don't know how to program and test, ie. the general non IT tech population is writing spreadsheets because they don't know another way to build these tools and they do it in the only way that they've taught themselves to do. If they knew better they often wouldn't start by clicking the excel icon in the first place.

Re:Spreadsheets - best and worst thing there is

[I'm New Around Here]

Introduce him to Treesheets. Maybe he'll leave you alone as he tries to figure it out.

Re:Spreadsheets - best and worst thing there is

[innerweb]

This is the sane way to do it, but it still falls on me to do this. I get spreadsheets from others and I *fix* them. I would rather they talk to me first and then I build it correctly and then things just work. *sigh*

Re:Spreadsheets - best and worst thing there is

[metlin]

Depends entirely on the industry and level of scrutiny your spreadsheet is likely to be subjected to.

In some industries, such as banking or consulting, spreadsheets are bread and butter. So, you'd better be prepared to have your models torn apart by everyone from fellow associates to senior partners (yes, they *actually* do review the models - especially when millions of dollars may be riding on them).

In those industries, much like coding, you design your models well and include a ton of documentation, include "test" sheets to validate that everything is working fine, minimize the use of "custom" macros, and keep functions at a modular, manageable level.

Re:Spreadsheets - best and worst thing there is

[BeerCat]

Yup, naming ranges has dug me out of a (self-inflicted) debugging hole on more than one occasion.

I find that another good rule of thumb is "do stuff (functions, documentation, formulae) so you can understand it in 6 months' time"

It's when someone asks me "can you just add a few bits on to this one you created?" for something I did literally years ago. That's when a good structured basis saves so much time.

Of course, sometimes I wonder "What was I thinking, when I did that?"...

Re:Spreadsheets - best and worst thing there is

[Boronx]

Spread sheets are such awesome tools that they allow non-programmers to create the same problems that noob programmers do while writing code.

Re:Spreadsheets - best and worst thing there is

[DavidHumus]

Good suggestion - much like a suggestion to apply a band-aid to a punctured artery.

You can follow these rules - and more - as rigorously as you please but still be undone by any number of simple things someone can do to a spreadsheet quite easily - like adding a row or column that looks like it's included in a calculation, but isn't.

Re:Spreadsheets - best and worst thing there is

[gordo3000]

doesn't always work. I spent years in Finance, and my first 2 were primarily writing and building spreadsheets to monitor risk, pnl, track drift in pnl, simulate portfolios, etc.

A couple years back my firm put into place a dev environment driven by python, and this was the first time since I was in middle school that I was programming withs something other than VBA or excel. After about 2 months, the same sheets I had been writing for years could be translated into massively simpler and easier to read (and follow, modify, extend) python.

Our problem was primarily driven by the need to keep all the information on one screen (in trading, you usually abhor having to scroll back and forth, as it takes time, and can lead to you searching for something rather than focusing on meaningful information). Of course, I could do all the calculations far to the right, and just do references that would be a mess on the dependency tree, but things seem to break just often enough to where you need to balance debugging on the fly (i.e. making it not impossible to see where your inputs are flowing to and which step they are breaking on). I never found a perfect balance.

Some folks decided to use VBA, but then I have never found it easy to read someone's hacked together VBA code. Python I find much easier, and this probably has to do with the weird inconsistent syntax in VBA.

And I'm well aware we were probably banging with a hammer to chop down a california redwood, but sometimes there is so much momentum in a firm you can't change how things are done (interoperability).

Re:Spreadsheets - best and worst thing there is

[hisstory student]

Good grief. Insightful? It's a joke folks!

Re:Spreadsheets - best and worst thing there is

[Boronx]

only kinda.

Sounds like job security.

[Rinikusu]

Dunno if that's a good or bad thing, though.

I've had to take over maintenance of a few "excel" based applications. Never. Again.

Re:Sounds like job security.

[mjwx]

Dunno if that's a good or bad thing, though.

I've had to take over maintenance of a few "excel" based applications. Never. Again.

That's Excel for you.

I use a lot of scripts that are based on CSV files for input, output and storage of values. You want to know what I edit them in... Notepad. Because Excel fucks around with it too much and I'm sick of the "but this is not in our proprietary format" dialogue when closing it (it also refuses to save on exit unless I change it to .xlsx). However the biggest sin Excel does (to me) is removing leading zeros, that number has to fix a N digit mask or it will fail.

Excel has grown into a terrible tool for spreadsheets, this does not make spreadsheets bad however.

Re:Sounds like job security.

[Rinikusu]

You should absolutely put in a Trigger Warning when you talk about Excel like that. ;)

So what's the alternative?

[Cyberax]

So what's the alternative? There are no good and easy to use software packages to create simple data-intensive apps. The closest alternative was VB6 and if I had to chose between it and Excel, I'd choose Excel any day of the week.

Re:So what's the alternative?

[DigiShaman]

File Maker Pro.

(trying not to laugh)

Re:So what's the alternative?

[simonbp]

Python plus Numpy. Plus Pandas if working with large amounts of data.

Re:So what's the alternative?

[rubycodez]

For those things that a spreadsheet does quickly and well, you could waste hours screwing around with Numpy

Re:So what's the alternative?

[geekoid]

Fortran. If you laugh, then you don't know much about advanced computing.

Re:So what's the alternative?

[Cyberax]

Nope. Numpy doesn't allow you to visually play with the data. You have to write code for everything.

Re:So what's the alternative?

[by (1706743)]

I know it's huge overkill, but I've had times where it was honestly easier to drop the data into PostgreSQL (MySQL, if you prefer) than edit it in Excel / Gnumeric / Open/LibreOffice's spreadsheet tool.

There was one case where my friend needed to analyze a modest amount of data -- 70k rows, 30 columns or so -- and Excel would absolutely choke on her new laptop running Excel. Dropped it into Postgres on my anemic netbook and queries were lightning fast. No need to specify column types, either -- just load everything as text and do query-time typecasting.

Clearly, choose the right tool for the job; but if you like separating the data from the logic / are comfortable with SQL / etc., I find it much more efficient to write a few lines of SQL to get the data I want (export the queries to a CSV, load 'em up with gnuplot [or Octave as a gnuplot frontend], and you have pretty vector-graphics). To each his/her own, though.

Re:So what's the alternative?

[mbkennel]

This problem, reproducible data analysis, has been solved before.

Decent alternatives to spreadsheets (which are entirely opaque) are (a) Matlab, (b) Mathematica notebook, (c) iPython notebook+numpy+pandas, (d) SAS/SPSS/R

Re:So what's the alternative?

[Cyberax]

Excel is reproducible. Microsoft worked very hard to make its floating point calculations work exactly the same way on all machines.

SPSS is nice, but it is expensive as hell.

Re:So what's the alternative?

[Balthisar]

Access.

People will laugh. But in an office environment it's an excellent solution. But one can still write formulas directly in reports and forms, so code review isn't necessarily easier.

Re:So what's the alternative?

[Breakerofthings]

Perl. When things get to messy for a spreadsheet, I whip up a little perl. Easier to repeat the calculations for different data sets, as a bonus. Access to much richer libraries, and you can shell out to GNUPlot, Ploticus, Asymptote, or whatever. Or Python, if that's your cup of tea ... or Ruby, even R ... whatever scripting language floats your boat.

Re:So what's the alternative?

[sjames]

That's what the GNUplot module is for.

Re:So what's the alternative?

[murdocj]

gack. Perl is one of the few things that make Excel look good.

Re:So what's the alternative?

[Bonobo_Unknown]

Perl no good for people that don't program.

Re:So what's the alternative?

[jd2112]

Access.

People will laugh. But in an office environment it's an excellent solution. But one can still write formulas directly in reports and forms, so code review isn't necessarily easier.

For those who don't understand relational database concepts, Access can be a machine gun for shooting yourself in the foot. The types of errors that typically find their way into Excel spreadsheets can get magnified several times over by moving to Access.
Those who do understand relational database concepts are probably putting their data in a real DBMS (MSSQL, Oracle, Postgres, MySQL, etc).

Re:So what's the alternative?

[camperdave]

One thing Access will never do that Excel excels at: scrambling your data. One wrong sort and your spreadsheet is toast.

Re:So what's the alternative?

[Dan East]

I very much doubt they had to "work very hard". You just link to your own software FP library is all. Back in Windows CE days before the mobile devices had math coprocessors, we had to link to software floating point libraries. Intel had their own compiler and linker libaries (that cost money, versus Microsoft's free eVC++ toolset), which vastly outperformed Microsoft's. Using a specific FP library instead of falling back on the default library (which uses hardware and thus where the calculation differences you mention arise) is hardly brain surgery.

Re:So what's the alternative?

[wezelboy]

And Django for a front end.

Re:So what's the alternative?

[Lotana]

Access has its place. Its front-end and integration with other office solutions ensure it is the best quick, light database solution for majority of small businesses.

But the emphasis is on "light". I am sure everyone has dealt with Access files that are used as whole business-tracking applications. Files that approach close to their limit of 4GB. Files that are simultaneously being used by all the users to record their timesheets in. And anytime it is suggested to move to a more proper database engine, everyone screams blood and tears because Access is all they know. It is too much risk to move to some other way, because they always done it this way.

Many slashdoters have IT background, so have dealt with this phenomenon. Unfortunately they blame the tool and not the conservative people. It is much easier to just demonize the software, rather than educate an entrenched user.

Re:So what's the alternative?

[qwak23]

In this house, we do not use the F-word!

Re:So what's the alternative?

[radarskiy]

Restrict programming to the indoctrinated priesthood.

Re:So what's the alternative?

[Gumpu]

You use something like SAS or R.

Re:So what's the alternative?

[bingoUV]

If they don't program, what are they doing around large quantities of data?

Re:So what's the alternative?

[Tom]

So what's the alternative?

Databases.

For anything that requires a formula more complicated then =SUM(A1:A10)/AVG(B1:B10) you should use something that was actually designed to handle data.

Re:So what's the alternative?

[Cyberax]

No, it's more complicated. X86 historically used 80-bit floating point numbers internally and translated to 64-bit as needed. That causes a lot of very subtle bugs if you're not careful - they do not affect the result too much, but they're there. It's easier now, because most CPUs simply use 64-bit floats.

Re:So what's the alternative?

[sonamchauhan]

I laugh. And you're correct: I don't know advanced computing.

But Fortran is still no alternative to spreadsheets.

Saying it is, is like saying SAP ABAP is an alternative to Microsoft Basic. Sure, at some deep fundamental level, it is. But you have to migrate environments, and more importantly, migrate the user interface and user skills.

Re:So what's the alternative?

[jma05]

The problem here (Piketty, as well as Reinhart and Rogoff) isn't simple, data-intensive apps (that would be a business app developer's problem, perhaps you are one). It's demonstrating an innovative, scientific analysis in an easy to review format. These economist papers aren't that data intensive... they usually have much less data than a typical business app.

https://gist.github.com/vincen...
(169K as uncompressed text)

Its the analysis that is the value here. The rather short, and a computationally non-intensive analysis in Reinhart and Rogoff paper triggered financial effects to the tune of probably trillions of dollars across Europe, some would argue prematurely.

The solution for this problem is a statistical package with a notebook presentation. The ideal case would probably be R with knitr. It allows one to combine snippets of code, with data, output and documentation to discuss the analysis & results in easy to understand chunks.

IPython notebook is also an excellent alternative.
Here is a demonstration of how Reinhart-Rogoff paper should have submitted the data.
http://nbviewer.ipython.org/gi...
I am sure, someone will do a Piketty one soon as well.

Re:So what's the alternative?

[afidel]

I find SQLite Database Browser is easier to use for something quick and dirty like that since it requires just a download of the exe instead of an installation and configuration plus installing another program to do the actual data manipulation.

Re:So what's the alternative?

[Bonobo_Unknown]

Working. There are far more people that work with large amounts of data than know how to program.

Re:So what's the alternative?

[bingoUV]

Can't be called working. It would be like my 6 year old "working" with vernier calipers - there has to be an understanding of the subject matter, and resulting pursuit of success, for something to be called "work".

You mean fiddling with data. Molesting the data.

Re: So what's the alternative?

[Tom]

Yeah, right, and Word was designed to write books, as anyone who's lost his doctoral thesis to it can attest to.

Word is good for letters, and Excel is a good 2D calculator. If you want to get real work done, get real tools. It's true in every field, not just computing.

Re:So what's the alternative?

[Bonobo_Unknown]

Thanks for that, you gave me a chuckle. I agree with you in spririt, but there are a lot of people out there molesting data in order to perform their work function and to get paid. Far more than have any understanding of data structures, algorithms or even basic data normalization. It's unfortunate.

Re: So what's the alternative?

[Tom]

It's 3D actually; each worksheet is a separate layer. And you completely neglect its charting and graphing abilities, plus it can automatically pull data from external sources (such as a database).

I know all of that, and yes, that, too, is useful at times. But as a graphing tool it's as bad as it is as a business logic tool. It's a great hack if you need a bar chart and you need it now and you don't care about the looks or it being very good.

Anyway, you're moving the goalposts with your whole letter vs. book analogy. Word may not be adequate for writing a complete novel, but it's still a word processor.

I was just making a metaphor. Word is good for short texts that don't need much structure, but it fails if you need to do "real work" (a book, thesis, etc.) -- same with Excel. It is a great tool for some simple work you need to get out of the way quickly, but if you do real work, better get something serious.

That's been my point all along, just tried to illustrate it with an example that might be easier to grasph than calculator vs. business logic.

This is like saying first class is the only way to

[SpaceCadetTrav]

Of course it is, but we can't afford to do it right.

Re:Is this worth reading? Answer: NO

[epyT-R]

Maybe a lack of all these bloated runtimes will help filter out the terrible programmers.

OOXML or Excelception

[tepples]

I think Excel stores formulas in a zipped XML document. Someone could write a tool that extracts each cell's formula from a workbook, sorts them topologically, and spits out JavaScript, Python, or whatever your favorite scripting language is.

Or you could make an Excel spreadsheet that lists formulas in other spreadsheets.

Re:OOXML or Excelception

[smittyoneeach]

Yeah, but if you don't implement the spreadsheet in PostScript and then embed it in a web server, are you really obfuscating it enough?

Re:OOXML or Excelception

[tepples]

Last time I tried saving to CSV in Excel, it saved the value to which the formula evaluates, not the formula itself.

Re:OOXML or Excelception

[viperidaenz]

If you're in Excel, you can drag the function bar down so it spans multiple lines. You're not limited to left-right scrolling or a single line at a time.
Probably also the same in other office suits.

Re:OOXML or Excelception

[ponos]

How can you possibly expect a CSV to contain a formula? It's a text file.

"spreadsheets" = computation program

[globaljustin]

ugh...so anger! always with the nomenclature distinctions...this is a stupid approach to a real problem

a spreadsheet is a computer program

that's it...

to criticize the act of entering data and performing computations on that data using computer software is the height of ignorance

I don't know if he's right or not, but this guy's real criticizm, once you fight through his ignorance of the issue is that in his view Pickety didn't show enough of how he got his figures...or more accurately, the TFA author had to look at the spreasheet cell to see what formula it used (gasp!)

The code is hidden away in dozens if not hundreds of little cells If you are not reviewing your code carefully and if you make it difficult for others to review it, how do expect it to be reliable?'"

so he probably doesn't know how to use the interface of a spreadsheet very well, which makes the act of checking a formula tedious...

then he writes some dumbass article inventing a problem to vent his frustration and reinforce his self-image...

all the while missing the real problem with economics "research" (not Pickety but others do this...) it's called "P-hacking"

P-hacking is the problem in social science/economics research, not using 'spreadsheets'

gah!

Re:"spreadsheets" = computation program

[geekoid]

Maybe you should read it again?
His real criticizm is that spreadsheet software is horrible for any high end work, or with anything you want to share, and he is correct.

"so he probably doesn't know how to use the interface of a spreadsheet very well, which makes the act of checking a formula tedious..."
it is tedious, even if you are an expert and even if the user uses goof practices.

"P-hacking is the problem in social science/economics research, not using 'spreadsheets'"
I don't think you know what P-Hacking is.

Re:"spreadsheets" = computation program

[murdocj]

I have bad news for you. Reading code is tedious. Period. You think crawling thru a bunch of files trying to relate functions is any easier?

Re:"spreadsheets" = computation program

[Attila Dimedici]

Actually his problem is that he is giving Picketty the benefit of the doubt. Piketty could have made it easy to check his formulas and calculations, but since that would have made it easy to see that the data did not support his conclusions, he did not do so. Actually I think that the author of this article is aware that the difficulty was likely intentional, but is making this argument in order to discourage future researchers from doing something similar. I think that the right way to get researchers to stop doing this sort of thing is to assume that those whose data processing method is obfuscated are doing so on purpose.

Re:"spreadsheets" = computation program

[dkf]

even if the user uses goof practices.

I think that's going to be close to 100% of all users...

Not "important work"

[LordLucless]

It's not "spreadsheets shouldn't be used for important work", it's "spreadsheets should not be used for work that's not suitable for spreadsheets". Tools for the job, and all that.

Re:Not "important work"

[tepples]

Or perhaps it's "very little work happens to be both important and suitable for spreadsheets."

Re:Not "important work"

[NikeHerc]

... "spreadsheets should not be used for work that's not suitable for spreadsheets".

I previously worked for a multi-billion dollar, multi-national company that everyone has heard of. They did the goofiest things imaginable with spreadsheets and thought they were providing us tools. It was similar to using jello to build a hundred foot tall tower.

As someone else wrote, most people who build spreadsheets have no business building spreadsheets.

I often convert the things to plain text files so I can use Unix or Linux tools such as grep, awk, less, and others.

Re:Not "important work"

[bingoUV]

A vast majority of work, even important work, is trivial, and spreadsheets won't have problems with trivial work.

So it's "very little work happens to be important, difficult, and suitable for spreadsheets".

Code reviewing a spreadsheet

[muhula]

If the inability to code review spreadsheets was a real issue, it wouldn't be too hard to convert spreadsheet functions into a functional language. For non-programmers, a spreadsheet lowers the barrier to entry. This allows people to do something useful and productive who couldn't do so otherwise. That's a good thing.

Re:Code reviewing a spreadsheet

[JustNiz]

>> For non-programmers, a spreadsheet lowers the barrier to entry.

Possibly, but this guy is a Computer Science prof. He should have already known much better.

Re:Code reviewing a spreadsheet

[geekoid]

For non programmers modern spread sheet give the user rope, with a noose already premade and a map on where to put your head.

Re:Code reviewing a spreadsheet

[dbIII]

IMHO he does know better and is telling people to stop trying to get a tangled web of spreadsheet formulas to do what MATLAB or any of a very long list of other things could do better.
Lots of people use a spreadsheet like a version of assembly with no access to registers, just memory locations.

Re:Code reviewing a spreadsheet

[IwantToKeepAnon]

For non programmers modern spread sheet give the user rope, with a noose already premade and a map on where to put your head.

Wait ... are we talking Excel or C?!?

Another major issues with spreadsheets

[mann17]

Another major issues with spreadsheets is that they don't handle data typing issues very well. For example, if you try to add a list of numbers, and somewhere in the list you have a number encoded as text, instead of throwing an error, it won't be included in the sum. Errors should never pass silently. Unless explicitly silenced.

Re:Another major issues with spreadsheets

[oneiros27]

I once saw this issue mitigated by keeping a cell that had the count of the non-number cells in the sum. If it wasn't zero, the cell was turned red, so you could easily see something was wrong.

They had a region of the spreadsheet dedicated to checks, which would all be colored either green or red, so you could easily glance at it and see if there were issues with the data entry.

Spreadsheets as a software development platform?

[Virtucon]

You're doing it wrong.

Excel is not a programming language

[Anonymous Coward]

There was a farmer who came up with a system for how much of which chemicals should be used to fertilize different areas of his farm. He built his system in Excel, and modified/evolved it over the course of many years. It eventually got so crazy-complex (and slow) (and unable to run in any version of Excel after 2007) that he hired my old company to port it into a shiny new software package.

I called it The Spreadsheet from Hell. And it was. Any plugin I tried to throw at Excel just to map cell dependencies would crash. Trying to comprehend how it worked took months, only to decide that it would be better just to take the core concepts and build something from scratch.

TLDR; Just say no to spreadsheets

Re:Excel is not a programming language

[Bonobo_Unknown]

TLDR; Just say no to spreadsheets

... for complex software engineering projects. It's okay for 99% of the stuff that regular people use it for.

Piketty's work will be done for him

[matbury]

The fact that Piketty's work describes a damning indictement of the USA's most cherished concept - free market capitalism - means that thousands of neo-liberal economists will pour over every single digit and operator in his spreadsheets looking for anything to negate the findings. If they can't find anything, they'll attack him. When you hear of character attacks against Piketty or some other diversionary tactic, you'll know his data is correct.

Re:Piketty's work will be done for him

[tomhath]

Other economics papers that reached similar conclusions such as the well known Growth in a Time of Debt also were based on flawed spreadsheets. It makes one question the entire hypothesis when the best known works on the subject are based on incorrect (or just plain fabricated) data.

Re:Piketty's work will be done for him

[Giant Electronic Bra]

Well, what makes you think that Gates, Buffet, or Slim work harder than anyone else? Clearly there is plenty of luck involved, so R can be greater than G but there is a LOT of noise. As for expecting the richest man to be a Rockefeller, who says the Rockefellers aren't vastly more wealthy than Gates or any other one of these people that Forbes lists? Do you think they keep their money around in places where it can be counted? Nobody has EVEN THE SLIGHTEST IDEA how much money the Rockefellers, the Rothschilds, the Fuggers, the Carnegies etc have. These great fortunes almost never die and entry into the top ranks of the world's wealthiest people is exceedingly difficult and rare.

I really don't know if Piketty's mistakes are all so dire as to erase his conclusions, or if they are warranted in the first place on the face of it, but I think you're very wrong about the nature of wealth and fortunes.

Re:Piketty's work will be done for him

[timeOday]

But if the top 1% changes every generation (and this is exactly what happens), is that as big of a problem as Picketty and other liberals make it out to be?

That is a key assertion, especially if broadened to, say, the top 20%. What is your source? All the studies I have seen say It is much harder for a poor child born in America to climb into the rare air of the countryâ(TM)s highest earners than it is for a similar child in, for example, Canada or Denmark.

Re:Piketty's work will be done for him

[timeOday]

Heh. They should just teach Econ 101, Physics 101, and Evolutionary Theory in Kindergarden and then quit while the students are at the peak of explanatory power, with everything seeming so nice and simple.

Re:Piketty's work will be done for him

[TheSync]

That is a key assertion, especially if broadened to, say, the top 20%. What is your source?

Here is some info:

61% of U.S. households will break into the top 20% of incomes (roughly $111,000) for at least 2 consecutive years.

39% of U.S. households will break into the top 10% of incomes (roughly $153,000) for at least 2 consecutive years.

5% of U.S. households will break into the top 1% of incomes (roughly $360,000) for at least 2 consecutive years.

That said, 20% of U.S. households will fall into poverty (roughly $23,850 for a family of 4) for at least 2 consecutive years..

Source info here.

Re:Piketty's work will be done for him

[u38cg]

And how do we tell the difference between that and him being wrong, if you please?

Re:Piketty's work will be done for him

[gordo3000]

best example is middle eastern money actually. I have a good friend from college who for years I never had any idea of the wealth and power of his family, and then I find out they were deep into oil money but you couldn't trace it at all. There was no listing of the potential wealth this family had, in fact the people weren't even mentioned. And you are talking about families (and because this money is new, the patriarch is still alive) that control billions and no one even lists on a world billionaire list.

Forbes lists the richest people whose wealth can be measured because primary asset ownership is known. This is easiest if you hold a large portion of a publicly traded company, and hence, they are all scions of people who build such companies. I would be amazed if the head of the Sauds is worth anything less than the combination of Buffet, Slim, and Gates; the king gives his wife an art budget of supposedly, 2 billion dollars a year. This is the kind of money even Buffet can't come up with as a side expense.

Undocumented wealth in this world is unbelievable in its scope. But its funny the GP actually thinks rich people just hand over their personal financial accounts to Forbes.

Re:Piketty's work will be done for him

[matbury]

It's the scientific method, i.e. lack of falsifying evidence. He's provided the evidence for, if nobody can find sufficient evidence against, then the hypothesis stands.

Re:Piketty's work will be done for him

[matbury]

Mmm... a critique by a self-professed promoters of the Austrian School of Economics. See this article for Paul Krugman's evaluation on the Austrian School: http://krugman.blogs.nytimes.c...

BTW, the Austrian School is very close to the Chicago School (AKA, neo-liberal capitalism as most people understand it today) in that both of them are based on assumptions and lack of supporting evidence or examples that show their predictions in action in the real world. Remember that infamous retraction by Milton Friedman after the 2008 crash, "I was wrong."?

I agree...

[MatthiasF]

I agree, a well made spreadsheet is far easier to follow than a proprietary program or even most study's results.

If you have a custom formula in a spreadsheet, create it in the program's scripting language instead of copy/pasting to tons of cells. Create the spreadsheet in a repeatable layout that is ease to understand the sections and the flow of the data.

I do not see how that is any different than using a proprietary program. At least with a spreadsheet you can look directly at the code for errors. In a proprietary program, you would need to learn what the behavior of the libraries or the specific nomenclature that is typically not always standardized.

The entire article reads more like a "why don't people use what I use" argument and not a reasonable critique.

Re:I agree...

[bingoUV]

While I am not great at spreadsheets, isn't it necessary to store data interleaved with logic/code if you use a spreadsheet? If it is, spreadsheets are naturally unfit for achieving high degrees of correctness and verifying if they have reached.

Re:I agree...

[MatthiasF]

No, it is not necessary. You can label ranges in spreadsheets and run code against them without the functions being intermingled. Most of the spreadsheet software has had this ability for at least fifteen years.

In my opinion this actually can make spreadsheets more correct, since you can share the spreadsheet easily and even watch the data as it is being manipulated by formulas, whereas most proprietary programs only give you the ending output. You can run different functions against the same ranges, direct their output to different sheets, etc.

There are a lot of options when it comes to modern spreadsheet software.

New type of tool needed?

[mars-nl]

The spreadsheet was a great invention and it can be very useful. But it's not useful for everything. There should be some kind of tool that allows you to write formulas programming style (e.g. python), but with some added table visualisation for inputting/outputting data.

Re:New type of tool needed?

[dbIII]

Early Excel had a nice little scripting language for that. Slightly later MS Excel had a different, totatally incompatible scripting language for that as well. Then there was another, totally incompatible scritping language that also let you do things with other parts of MS Office as well. I lost track after that and it seemed that by then not many people were using native scripting tools with MS Excel. There were some third party things that kept their front end the same through each change of the MS back end

Re:Spreadsheets as a software development platform

[preaction]

Tell that to the entire finance and insurance industry.

You can't audit spreadsheets

[swm]

I figured this out twenty-mumble years ago.
I was doing data analysis in spreadsheets, and realized that I had no way to audit them.
The data and the analysis were all just...there...in the spreadsheet.

As soon as I got a grip on my data, I changed over to C programs that I could test, and document, and validate, and run at any time to demonstrate that input X generated output Y.

Re:You can't audit spreadsheets

[freeze128]

As I recall, MS Excel ships with an add-in called "audit", but it's not installed by default.

Blame the tool...

[Diddlbiker]

My father was a wise man, and a solid programmer. He liked Basic, because it was simple, and readable (in his environment the alternatives were mainly Assembler, Cobol, and RPG). Whenever people made fun of his love for Basic, and how it resulted in bad code, he always replied “there are no bad languages, just bad programmers.”

The problem isn't the spreadsheet. The problem is people building ugly models in it. Do they seriously think that if those models were written in C, Java or Perl they would have been magnitudes better? I doubt it; you're just transplanting bad habits onto a different platform.

Of course, if he'd used trained professionals to build his models in whatever language of choice the models would be better. If he'd used trained professionals to build his spreadsheet models they would have been better as well.

Re:Blame the tool...

[Luckyo]

The "it's not the tool, it's the people" argument has one major flaw.

Tools are built so that people can perform tasks they can't otherwise do. As a result, if tool fails because it's not good enough for the task, at least part of the blame lies with tool and its creator.

Re:Blame the tool...

[J. J. Ramsey]

there are no bad languages, just bad programmers.

There are, however, languages that make it far easier to write code that is less readable and harder to maintain. As a specific example, compare Fortran 77 with Fortran 90. I can write the latter without any need for numerical statement labels. I can write a straightforward "DO WHILE" loop in Fortran 90, while in Fortran 77, I'd have to use the dreaded GOTO to get the same effect. Aside from basic stuff like that, I can write formulas in Fortran 90 with whole arrays, which can really help readability. In short, it is far easier to write clear code in Fortran 90 than in Fortran 77.

Do they seriously think that if those models were written in C, Java or Perl they would have been magnitudes better?

Heck, yes! For one thing, in any of those languages, separation of code and data -- something which spreadsheets actively discourage -- would be much easier.

Re:open source your sheet

[MightyYar]

I've actually written a very limited version of this. My boss likes to prototype algorithms in Excel, but I need to cram them into a machine with instructions written in a scripting language. I first use a VBA tool to tokenize and collect the Excel formulas, then over to Python to do some conversions of a few built in fuctions, then run it through a symbolic algebra toolkit (Sympy). Sympy has a nice feature where it can format its output as c-code. At that point, if I were using C I would be all done, but I have another translation step to do. Still, it beats the heck out of manually running through the stupid spreadsheet.

Misleading title

[gnu-sucks]

I think the title should be "Why You Shouldn't Use Spreadsheets for *Complicated* Work". Just because a job is important doesn't mean the calculation is complex and something that needs to be coded in, for example, matlab.

If my job is to make a pie chart, I can't see why using Excel is a bad idea. On the other hand, if I am examining the variance of several thousand data points and then plotting the residuals from a gaussian fit, then yes, I can see why using something else would be a lot better. It has nothing to do with importance. Only complexity.

Some things stick

[TapeCutter]

I recall a survey of (non-trivial) corporate spreadsheets in the mid-90's, it went something like 95% had a maths bug, in 80% of cases the bug made the sheet useless, 50% of the spreadsheets were used to make (incorrect) financial decisions. The reason why corporations coffers don't evaporate is that they use thousands of them so the +/-ve affect on the money buffer has a central limit of zero. It's a much more precarious situation if you using a single homespun spreadsheet to run a corner store

Re:Some things stick

[viperidaenz]

Were the survey results collated on a spreadsheet?

Re:Some things stick

[peragrin]

nope Access Database.

now which is scarier?

Re:Some things stick

[Anonymous Coward]

powerpoint

Re:Some things stick

[phantomfive]

That is fascinating

Re:Some things stick

[timeOday]

The question is whether having the logic squirreled away in code or a DB would have made it more correct, which is a big assumption!

I really think Piketty deserves a lot of credit for releasing his "source" spreadsheets on such a substantive and controversial work. Most authors do not. If the critiques turn out to be substantial and extensive, I plan on waiting for a second edition with corrections before investing time in reading it.

Re:Some things stick

[turbidostato]

"The question is whether having the logic squirreled away in code or a DB would have made it more correct"

Then the answer is "it doesn't really matter". Of course the code can be as wrong as the spreadsheet but the point is that code is much easily auditable so, if you are using it for something important, you can *effectively* through more resources at it, which is basically impossible to be done on a spreadsheet.

Re:Some things stick

[timeOday]

Basically impossible to audit? This article is about the Financial Times' audit of the spreadsheet.

Re:Some things stick

[Sarten-X]

I've done audits on spreadsheets. They're not terribly difficult, and I dare say they're easier than many of the code reviews I've been through.

The most important thing is to understand how to use the spreadsheets. Either use separate worksheets for each major step in the calculation, or at least separate the computations using extra blank space. That serves the same function as code blocks, breaking up the computation into smaller, more manageable, pieces. Each small piece can be audited separately, and it provides a clear trail of how one number becomes another.

Next, use your formatting, even if it's not in a worksheet ever intended for public viewing. I'm particularly a fan of using conditional formatting to highlight the cells in a sheet (especially minimums and maximums) that will be passed on to the next worksheet. Then it's easy to check that the correct values are being passed, and the intermediate values all make sense.

Finally, use your fill tools correctly to ensure that the same computation is being applied to all cells. You should be able to audit the top of your worksheet and fill down to the bottom, without any formatting or visual elements getting in the way, and know that the whole worksheet is correct. When reviewing an old worksheet, note that Excel will highlight (with a green corner mark, as I recall) cells that don't fit the pattern.

Finally, remember that writing an algorithm for a spreadsheet has some of the same pitfalls as any other implementation. Double-check any function of which you're not certain the parameters. Put comments in non-obvious areas. Don't be too clever, and of course, if someone else can't understand your brilliance, you're not being brilliant.

Re:Some things stick

[hawkinspeter]

Is there an easy way to determine if the same formula is filled/copied to a whole column? I've hit problems in the past where an incorrect formula appeared in the middle of a column and threw off the results.

Re:Some things stick

[meglon]

The most important thing is to understand how to use the spreadsheets.

The most important thing is to understand how to use the spreadsheets.

The most important thing is to understand how to use the spreadsheets.

Knowledge is power.

Re:Some things stick

[MancunianMaskMan]

Excel checks formulas for "consistency" so if you have b1 : = a1+1 , b2: =a2+1 , b3: =a42 + 1 , b4: =a4+1 , then the ropey B3 will be flagged up. Of course there are sometimes false positives and you switch this check off or ignore it, and who knows how many false negatives.
The message "just say no to Excel" still stands.

Re:Some things stick

[Tom]

You don't by chance remember the name of the survey or have a link?

I'd be extremely interested in this particular piece.

Re:Some things stick

[Plumpaquatsch]

I recall a survey of (non-trivial) corporate spreadsheets in the mid-90's, it went something like 95% had a maths bug, in 80% of cases the bug made the sheet useless, 50% of the spreadsheets were used to make (incorrect) financial decisions.

And using a proper programming language prevents math bugs how?

Re:Some things stick

[turbidostato]

"They're not terribly difficult, and I dare say they're easier than many of the code reviews I've been through."

So, please, tell me at a glance what did change in the spreadsheet (both contents and formulae) and why since yesterday.

After that, please, tell me how can commonalities be extracted from a spreadsheet (say, a worthnoting formula or workflow) so it can be applied to another dozen spreadsheets I happen to have over there, both past and future.

Re: Some things stick

[soluzar296]

It does not. It can make the bug easier to find, though. When you look at source code you can get an easy overview of the program flow. In a speeadsheet, code is divided between an arbitrary number of cells on an arbitarary number of sheets, and the flow is far less obvious.

Re:Some things stick

[WolfWithoutAClause]

A3 = A6 * B6 + C8

versus:

sum = numberOfItems * costOfItem + salesTax

While you can *force* a spreadsheet to work like that, it's not the default, and the default makes it so very much easier to fuck up.

Re:Some things stick

[Will.Woodhull]

The easiest way is to make a temporary copy of the spreadsheet and use the "copy downward" capabilities to fill the formula columns in the tempsheet. Then compare the results to the original.

Re:Some things stick

[DarthVain]

That has nothing to do with spreadsheets, and more to do with people not understanding, math, business, or trying to push some agenda using BS statistics.

I reviewed a spreadsheet that statistically *proved* that a project was succeeding excellently. What it actually proved is that the project was a complete failure as it made absolute zero significant difference to actual operations. When I confronted the guy, and said, you know this is a huge lie right, that it is totally misrepresenting the values, he just made excuses, hand gestures and a lot of shrugging motions. It was presented to management and they all loved it. Later when it was brought up by management with me, I told them directly that it was complete bullshit. They don't care. They can show the same BS numbers to their managers, and get kudos. Everyone gets a feel good slap on the back and stuff for the CV.

So I don't know what the percentage might be, but I suspect a lot of *mistakes* have little to do with spreadsheet software, math skills, or even anything other than a collective desire for some decision to be justified.

Re:Some things stick

[ImprovOmega]

PowerPoint implementing spreadsheet-like functionality using VBA on the backend.

Re:open source your sheet

[sjames]

That's not the problem. The real issue is that spreadsheets are effectively a big ball of goto with multiple entry points.

can or cannot compute

[globaljustin]

His real criticizm is that spreadsheet software is horrible for any high end work, or with anything you want to share, and he is correct.

you're wrong on both counts...that is not his 'real' criticism and even if it was he and you would still be wrong

spreadsheets are ***computation software***

if it can execute the operation needed for the research then it is acceptable...if not, then no

end.

it's a tool to analyze data...that's ****all any of these programs are, ever****

the method of analysis is either proper or not to test the hypothesis....that is a *completely different question* to whether the software is physically capable of doing the computation

you can't blame a spreadsheet for a poorly devised experiment...you *can* blame a researcher for using an inappropriate statistical model...you *cannot* criticize the method of analysis as long as it is physically capable of the computation

your echo TFA's ignorance...and yes, I've seen people like you use rhetoric like yours to justify P-hacking throughout my time in academia

Re:can or cannot compute

[tepples]

if it can execute the operation needed for the research then it is acceptable...if not, then no

I think geekoid is trying to say that even though spreadsheets can in theory "execute the operation needed for the research", practical limits inherent in the spreadsheet user interface make it difficult to verify that what the spreadsheet is calculating matches what you wanted to calculate. Consider this: An 8-bit microcomputer "can execute the operation needed for the research" but that doesn't make it the best tool.

Re:can or cannot compute

[dbIII]

spreadsheets are ***computation software***

So when did Slashot turn into a "the beige box is a hard drive because I say it is and fuck you elitist technical folk" site?

Re:can or cannot compute

[Baloroth]

if it can execute the operation needed for the research then it is acceptable...if not, then no

You could probably write this computational code in a shell script, too. But it would still be a terrible idea. Why? Because it's the wrong tool for the job. Simple as that. It doesn't matter what you can and cannot do, it matters what you should do, and you shouldn't use spreadsheets for anything complicated. It's simply too easy to make stupid mistakes that are difficult to trace and correct (or even notice).

you can't blame a spreadsheet for a poorly devised experiment...you *can* blame a researcher for using an inappropriate statistical model...you *cannot* criticize the method of analysis as long as it is physically capable of the computation

TFA isn't blaming the spreadsheets, he's blaming the people who use them for using them. It's not acceptable to use a tool that works poorly and is highly susceptible to mistakes, and no one should listen to anyone who does so unless that person is damned good at that tool: yes, it is possible that someone is so fantastically good with spreadsheets they can use them for massive data analysis with no problems. They are, however, the exception, and I would generally be inclined to disbelieve the results from anyone who does large work with spreadsheets (simply because of the possibility for errors and the lack of concern for accuracy that using spreadsheets demonstrates). So, the conclusion is that you shouldn't use spreadsheets for important work. You absolutely can criticize an analysis if it uses a tool that is highly likely to introduce errors, and that's fundamentally the point (and it's underscored by the fact that that is precisely what happened in Piketty’s case).

Re:Spreadsheets as a software development platform

[Virtucon]

is that why we're fucked?

Dear "Computer science professor",

[Cammi]

Dear "Computer science professor", How about you earn your title, instead of trolling about something you know nothing about. Yours truly, People who work for a living.

A Formula only an Actuary could Love

[turp182]

There are no corporate secrets below, but I stumbled upon this formula in an actuarial spreadsheet (I'm a developer with an actuarial education).

The only way this logic could be verified is by breaking the single formula into 20+ different cells with more simple calculations.

And of course it is in several thousand cells, bringing any computer at all to its knees during calculation.

A good example of how not to use Excel (but the actuaries don't have access to IT prototyping or core development).

=IF(F6="050",tiers!$D$21+IF(AND(F6="050",OR(E6="W",E6="X")),tiers!$D$29-tiers!$D$26+VLOOKUP("N"&100&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&"N/A"&M6/12&"/"&N6&"0-"&R6/1000&C6*1,tiers!$L$2:$W$20969,12,0),IF(OR(E6="W",E6="X"),VLOOKUP("N"&F6*1&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&"N/A"&M6/12&"/"&N6&"0-"&R6/1000&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6="050",E6="P",M6=36,N6=60),tiers!$D$29-tiers!$D$26+VLOOKUP("E"&100&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&"N/A"&M6&"/"&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(E6="P",M6=36,N6=60),VLOOKUP("E"&F6*1&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&"N/A"&M6&"/"&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6="050",E6="P"),tiers!$D$29-tiers!$D$26+VLOOKUP("E"&100&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&"N/A"&M6/12&"/"&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(E6="P",VLOOKUP("E"&F6*1&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&"N/A"&M6/12&"/"&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6="050",E6="*",M6=6),tiers!$D$29-tiers!$D$26+VLOOKUP(B6&100&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&IF(Q6=48,"0-4","6-10")&M6&"/"&N6&"0-100"&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(E6="*",M6=6),VLOOKUP(B6&F6*1&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&IF(OR(L6="PPH08",L6="PTH08"),"0-9",IF(Q6=48,"0-4","6-10"))&M6&"/"&N6&IF(R6=125000,"100-125","0-100")&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6="050",E6="*"),tiers!$D$29-tiers!$D$26+VLOOKUP(B6&100&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&IF(OR(L6="PPH08",L6="PTH08"),"0-9",IF(Q6=48,"0-4","6-10"))&M6&"/"&N6&IF(AND(OR(L6="PPH08",L6="PTH08"),R6=100000),"0-100",VLOOKUP(R6,tiers!$B$64:$C$70,2,0))&C6*1,tiers!$L$2:$W$20969,12,0),IF(E6="*",VLOOKUP(B6&F6*1&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&IF(OR(L6="PPH08",L6="PTH08"),"0-9",IF(Q6=48,"0-4","6-10"))&M6&"/"&N6&IF(AND(OR(L6="PPH08",L6="PTH08"),R6=100000),"0-100",VLOOKUP(R6,tiers!$B$64:$C$70,2,0))&C6*1,tiers!$L$2:$W$20969,12,0),"ERROR")))))))))),IF(AND(F6="050",OR(E6="W",E6="X")),tiers!$D$29-tiers!$D$26+VLOOKUP("N"&100&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&"N/A"&M6/12&"/"&N6&"0-"&R6/1000&C6*1,tiers!$L$2:$W$20969,12,0),IF(OR(E6="W",E6="X"),VLOOKUP("N"&F6*1&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&"N/A"&M6/12&"/"&N6&"0-"&R6/1000&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6="050",E6="P",M6=36,N6=60),tiers!$D$29-tiers!$D$26+VLOOKUP("E"&100&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&"N/A"&M6&"/"&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(E6="P",M6=36,N6=60),VLOOKUP("E"&F6*1&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&"N/A"&M6&"/"&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6="050",E6="P"),tiers!$D$29-tiers!$D$26+VLOOKUP("E"&100&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&"N/A"&M6/12&"/"&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(E6="P",VLOOKUP("E"&F6*1&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&"N/A"&M6/12&"/"&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6="050",E6="*",M6=6),tiers!$D$29-tiers!$D$26+VLOOKUP(B6&100&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&IF(Q6=

Re:A Formula only an Actuary could Love

[Bonobo_Unknown]

Oh the horror.

Re:A Formula only an Actuary could Love

[labnet]

Wow!
If I was in my early 20's, I'd probabbly think I was 'leet'
Now in my mid 40's, I'd probabbly fire whomever wrote it.

Re:A Formula only an Actuary could Love

[Waffle Iron]

What is the end result for this one cell?

42

Re:A Formula only an Actuary could Love

[nuckfuts]

Evidently no Douglas Adams fans around to mod you up.

Re:A Formula only an Actuary could Love

[SpzToid]

I am seeing so many tiers in there, my eyes are bleeding profusely.

Re:A Formula only an Actuary could Love

[Anonymous Coward]

Philosophically yes this formula is disgusting, but if you are good enough at excel, understand the intended purpose of the calculation AND have some sort of intuition about the insurance product/situation that is being modeled, it can be verified. I used to be an actuary and many of my colleagues were excellent at auditing things that that looked just like this.

Everyone knows and can run an excel spreadsheet - this is a major advantage over writing code in a business environment.

Re:A Formula only an Actuary could Love

[dotancohen]

Wow!
If I was in my early 20's, I'd probabbly think I was 'leet'
Now in my mid 40's, I'd probabbly fire whomever wrote it.

The truth is, that Excel cell reminds me of a Bash monstrosity that I banged out a few weeks ago to test once per hour the state of a MySQL database to see if a long-running DDL query had finished, and then to run another (complex and long-running) DDL query. This one-liner included a for loop, a while loop, two SQL queries, sleep, grep, sort, awk, and some other bits and pieces. At the time I thought it 1337 as well, but looking back at it I recognize it for the hack that it was.

However, Bash commands are inherently ephemeral whereas the Excel function would likely have been continued to be used for years down the road. This is where the difference between "don't need to be maintained" and "cannot be maintained" manifest!

Re:A Formula only an Actuary could Love

[wickerprints]

As someone who also has an actuarial background, the funniest thing about this sort of tangled code is that, in the mind of the analyst who wrote it, the intent was probably to do something relatively simple. Actuarial spreadsheets are some of the most convoluted things I've ever seen done in Excel.

Re:A Formula only an Actuary could Love

[turp182]

Obviously I agree...

My first "corporate" job was at a life insurance company, on an IT team that was dedicated to and sat with the Finance department (actuaries and accountants, I was in school and sitting for exams still).

I realized a few years ago that this was a great situation. The financial folks had a team of senior IT staff (I was the first IT intern, but I had two actuarial exams under my belt by my sophomore year and could code) who could undertake special projects, manage code, and keep the spreadsheets under some sort of control.

 

Re:A Formula only an Actuary could Love

[oodaloop]

=IF(F6="050",tiers!$D$21+IF(AND(F6="050",OR(E6="W",E6="X")),tiers!$D$29-tiers!$D$26+VLOOKUP("N"&100&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&"N/A"&M6/12&"/"&N6&"0-"&R6/1000&C6*1,tiers!$L$2:$W$20969,12,0),IF(OR(E6="W",E6="X"),VLOOKUP("N"&F6*1&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&"N/A"&M6/12&"/"&N6&"0-"&R6/1000&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6="050",E6="P",M6=36,N6=60),tiers!$D$29-tiers!$D$26+VLOOKUP("E"&100&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&"N/A"&M6&"/"&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(E6="P",M6=36,N6=60),VLOOKUP("E"&F6*1&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&"N/A"&M6&"/"&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6="050",E6="P"),tiers!$D$29-tiers!$D$26+VLOOKUP("E"&100&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&"N/A"&M6/12&"/"&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(E6="P",VLOOKUP("E"&F6*1&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&"N/A"&M6/12&"/"&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6="050",E6="*",M6=6),tiers!$D$29-tiers!$D$26+VLOOKUP(B6&100&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&IF(Q6=48,"0-4","6-10")&M6&"/"&N6&"0-100"&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(E6="*",M6=6),VLOOKUP(B6&F6*1&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&IF(OR(L6="PPH08",L6="PTH08"),"0-9",IF(Q6=48,"0-4","6-10"))&M6&"/"&N6&IF(R6=125000,"100-125","0-100")&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6="050",E6="*"),tiers!$D$29-tiers!$D$26+VLOOKUP(B6&100&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&IF(OR(L6="PPH08",L6="PTH08"),"0-9",IF(Q6=48,"0-4","6-10"))&M6&"/"&N6&IF(AND(OR(L6="PPH08",L6="PTH08"),R6=100000),"0-100",VLOOKUP(R6,tiers!$B$64:$C$70,2,0))&C6*1,tiers!$L$2:$W$20969,12,0),IF(E6="*",VLOOKUP(B6&F6*1&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&IF(OR(L6="PPH08",L6="PTH08"),"0-9",IF(Q6=48,"0-4","6-10"))&M6&"/"&N6&IF(AND(OR(L6="PPH08",L6="PTH08"),R6=100000),"0-100",VLOOKUP(R6,tiers!$B$64:$C$70,2,0))&C6*1,tiers!$L$2:$W$20969,12,0),"ERROR")))))))))),IF(AND(F6="050",OR(E6="W",E6="X")),tiers!$D$29-tiers!$D$26+VLOOKUP("N"&100&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&"N/A"&M6/12&"/"&N6&"0-"&R6/1000&C6*1,tiers!$L$2:$W$20969,12,0),IF(OR(E6="W",E6="X"),VLOOKUP("N"&F6*1&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&"N/A"&M6/12&"/"&N6&"0-"&R6/1000&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6="050",E6="P",M6=36,N6=60),tiers!$D$29-tiers!$D$26+VLOOKUP("E"&100&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&"N/A"&M6&"/"&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(E6="P",M6=36,N6=60),VLOOKUP("E"&F6*1&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&"N/A"&M6&"/"&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6="050",E6="P"),tiers!$D$29-tiers!$D$26+VLOOKUP("E"&100&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&"N/A"&M6/12&"/"&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(E6="P",VLOOKUP("E"&F6*1&VLOOKUP(L6,tiers!$B$52:$C$55,2,0)&"N/A"&M6/12&"/"&N6&VLOOKUP(R6,tiers!$B$57:$C$59,2,0)&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6="050",E6="*",M6=6),tiers!$D$29-tiers!$D$26+VLOOKUP(B6&100&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&IF(Q6=48,"0-4","6-10")&M6&"/"&N6&"0-100"&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(E6="*",M6=6),VLOOKUP(B6&F6*1&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&IF(OR(L6="PPH08",L6="PTH08"),"0-9",IF(Q6=48,"0-4","6-10"))&M6&"/"&N6&IF(R6=125000,"100-125","0-100")&C6*1,tiers!$L$2:$W$20969,12,0),IF(AND(F6="050",E6="*"),tiers!$D$29-tiers!$D$26+VLOOKUP(B6&100&VLOOKUP(L6,tiers!$B$38:$C$49,2,0)&IF(OR(L6="PPH08",L6="PTH08"),"0-9",IF(Q6=48,"0-4","6-10"))&M6&"/"

Re:A Formula only an Actuary could Love

[SteveAstro]

++++Redo from start
Out of cheese error

Re:A Formula only an Actuary could Love

[jma05]

True, but some unintentionally end up encouraging it and some discipline against it by design.
Its not the possibility that matters, but probability.

Re:A Formula only an Actuary could Love

[Azaril]

That's not bad, I've seen worse, but I do this sort of thing professionally. You could streamline that down hugely just by working through it with a flow chart, maybe using references. Most of the vlookups are duplicated serveral times. Vlookups are also less efficient (and less useful) than match/offset combos. I bet right now I could break that down into four or five cells and have it running 100x faster. You guys should hire someone to do this professionally if this is the kind of thing that gets used for important calculations.

Re:A Formula only an Actuary could Love

[DocSavage64109]

To be fair, that could be made much more readable using some logical carriage returns and indentation. I think alt-enter adds a newline in excel.

Re:A Formula only an Actuary could Love

[gordo3000]

any half decent coder would have broken it up into 20 different cells, probably something like DA-->DT with meaningful column names and a comment in each one and then combine it together way over on the left for whatever you want to see........

But you can do the exact same thing in python and I see it all the time, where people get lazy (oh wait, there is some minuscule savings of milliseconds, so it's worth it....) and put into one line what would be a lot more nicely done across several.

Luckily excel makes it nice and easy to segment the nested code as it highlights which parens tie out to other parens. So I can fix the crappy nesting that my juniors would write for me and then tell them if they want to keep their job they won't make me waste time making their tools readable and maintainable.

Understand their uses and limitations ...

[MacTO]

Lemire is right, spreadsheets are terrible for complex models that need to be modified. He is right for precisely the reasons he outlined.

That doesn't mean that spreadsheets are useless. If you have a standard form where you're only modifying values, rather than functions, spreadsheets are great. There is a low barrier to entry and they are good for communicating results. But as soon as you need to audit or modify functions, you are jumping all over the place and it is easy to make mistakes. Yes, there are ways to consolidate your code (at least in spreadsheets that support scripting), but you are going to take so much time learning how to use the advanced features of you spreadsheet that may as well learn a dedicated programming language in those cases.

And the reality is that it's pretty easy to learn how to use programming languages these days. Not as easy as using a spreadsheet, to be sure, but even the standard Python distribution can handle most of the vulgarities of loading data into memory and storing it properly (i.e. you don't have to worry about parsing or data structures too much). By adding the appropriate modules you can do some decent visualization of data. In some cases the visualization will be better than spreadsheets, and in others spreadsheets will have the lead. And that's just Python, which I chose as an example because I'm familiar with it. The reality is that there are much more appropriate domain specific languages out there.

criticizing cars b/c you can't use a brake pedal

[globaljustin]

Consider this: An 8-bit microcomputer "can execute the operation needed for the research" but that doesn't make it the best tool.

thanks for the input but this is still the wrong analogy...

it is not what TFA is saying, and it is incorrect in fact

Picketty is being criticized in TFA because he used a spreadsheet, which has 'cells' which contain 'formulas' which are descriptions of mathematical operations on data

TFA author is saying that, I quote again:

The code is hidden away in dozens if not hundreds of little cells If you are not reviewing your code carefully and if you **make it difficult for others to review it**, how do expect it to be reliable?'"

navigating a spreadsheet program to view a formula in a cell is *basic operation of the software*

if you cannot do that, you cannot properly use the software at all...it's like saying cars are unfair because some people don't understand how to use the brake pedal

sure, when research is "difficult to review" that is a bad thing....**EVERYONE AGREES ON THAT**...the fact that Picketty used a spreadsheet and TFA author can't use it's most basic functions is not worth discussing at all, ever

the problem in economics & social science research is P-hacking...gaming the results using your fancy 'non-spreadsheet' research software...that's a legit problem that we need more TFA's about

Re:Piketty's real problem isn't spreadsheet-relate

[Anonymous Coward]

Yeeaaahhhh you lose.

http://www.huffingtonpost.com/2014/05/27/piketty-not-wrong_n_5397358.html

Krugman was joined by economists Justin Wolfers, James Hamilton, Gabriel Zucman, frequent Piketty critic Scott Winship and others, along with The Economist's Ryan Avent, The Washington Post's Matthew O'Brien, and The New York Times' Neil Irwin, to name a few.

Re:open source your sheet

[Giant Electronic Bra]

No they aren't, they're constraint based systems. flow of control isn't the focus. You could easily reduce a spreadsheet to a bunch of Prolog or to almost any functional language.

What you're really saying is

[CriminalNerd]

"I never worked in a company with normal people."

I'm guessing you haven't had the pleasure of working in the typical firm where the company's years-old ENTIRE lifetime of work and data is passed around e-mail as a 80MB Excel attachment.

Re:What you're really saying is

[zugmeister]

... where the company's years-old ENTIRE lifetime of work and data is passed around e-mail as a 80MB Excel attachment.

This... is retarded enough to loop all the way around the spectrum and land squarely on awesome.

Re:What you're really saying is

[SpzToid]

Well okay but, how did they manage to email 80mb files successfully in the first place? That's a corporate I.T. feat right there.

Re:What you're really saying is

[qwak23]

But only 80MB?

No Excel file should use less than the system's available RAM.

Re:What you're really saying is

[LordLimecat]

"What, whats wrong with storing each day's sales data going back several years in separate worksheets in a single excel document?"

Re:What you're really saying is

[coolsnowmen]

Props to the email server admins that handle ~100MB attachments.

Re:What you're really saying is

[plover]

"What, whats wrong with storing each day's sales data going back several years in separate worksheets in a single excel document?"

Works for bitcoin.

You too can lose data and never know it.

[sir-gold]

I did some computer work for a company that used a single giant spreadsheet for their entire inventory system. Every time they added or removed something from the warehouse they would open this spreadsheet, edit it, and then overwrite the old file.

Somewhere along the line this file didn't save right, and dozens of columns in the spreadsheet were lost, but because it was way out in the double-letter columns, nobody noticed it for months (or even years, we never did pin it down)

I spent 2 weeks going though every single backup they had trying to find an old copy with the missing columns.

Macro / Scripting

[mfh]

Everyone should learn how to write macros ie: script. You can easily make a very functional program in VBA to do most data processing you would need and even store the code in a separate file that can be used by other sheets/workbooks and modified or updated between projects.

I would never suggest putting processing into a cell, unless you were just trying to figure something out fast.

I look at Excel as a sandbox. Some people can make awesome sand castles and others struggle.

Re:Macro / Scripting

[I'm New Around Here]

Would you be able to make an Excel spreadsheet that can solve a Sudoku puzzle, using the puzzles in the newspaper as the starting grid?

Re:Macro / Scripting

[Kaenneth]

Yes, I did that while bored once.

Re:Macro / Scripting

[RabidReindeer]

Would you be able to make an Excel spreadsheet that can solve a Sudoku puzzle, using the puzzles in the newspaper as the starting grid?

The very first time I saw Excel (on a Mac), the demo was a working analog clock.

Re:Macro / Scripting

[TheLink]

There used to be a pacman game that ran in excel.
http://technabob.com/blog/2008...
http://www1.plala.or.jp/chikad...
Not sure if it still works.

Probably many other excel games if you look around.

I Modified Mediawiki To Do "Spreadocs"

[Baldrson]

Lemire says:

Spreadsheets make code review difficult. The code is hidden away in dozens if not hundreds of little cells If you are not reviewing your code carefully and if you make it difficult for others to review it, how do expect it to be reliable?

I agree with this assessment which is why the algae macroengineering part of the Diogenes Institute's comprehensive plan for energy independence and the environment was done with a customization of Mediawiki that lets each "cell" be a compete document with the value taken by the model, the formula and comments with citations.

The "cell" identifiers are complete names suffixed with the units in which the quantity is expressed.

See The full "spreadsheet" page for an idea of how this plays.

Formula or formulas?

[tepples]

navigating a spreadsheet program to view a formula in a cell is *basic operation of the software*

I noticed you used the singular of "formula" and "cell". Is it quite as basic to be able to view multiple formulas in multiple cells?

Spreadsheets destroy data

[Dwedit]

Spreadsheets tend to mess with strings that look somewhat like a date, it will automatically convert it to a date when it sees things like that. You need to be really careful about spreadsheets automatically reformatting your data, make sure you properly indicate whether a field is Text or not.

Re:Spreadsheets destroy data

[RDW]

Spreadsheets tend to mess with strings that look somewhat like a date, it will automatically convert it to a date when it sees things like that. You need to be really careful about spreadsheets automatically reformatting your data, make sure you properly indicate whether a field is Text or not.

e.g. the infamous 'Excel genes', when a gene name like SEPT1 is silently converted to numerical date format:

http://www.biomedcentral.com/1...

http://nsaunders.wordpress.com...

Excel makes it far too easy for this to happen (just opening and saving a .csv file with Excel will silently corrupt it instead of invoking the data import wizard that would give you a chance to set data types per column - a great design decision!), and it's hard to spot corrupted cells if you have a list of hundreds or thousands of genes. Some of these have made their way into major online genetic databases:

http://www.biomedcentral.com/1...

Excel in bioimformatics? - just say no, kids.

Happened to a company I worked for

[Anonymous Coward]

Spreadsheet bug the end result of which was layoffs when the company was suddenly out of money.

The main thing for me is...

[wezelboy]

Someone correct me if I am wrong, but I have yet to see an Excel sheet that is ACID compliant. You rely on that for your core business and you are screwed.

Re:Piketty's real problem isn't spreadsheet-relate

[Capsaicin]

"Oh, that's just a right-wing smear from EVUL RETHUGLICANS!!!"?

Well the part about figures being constructed "out of thin air" is a smear (whoever it may be who claims it), as becomes clear when one reads the rest of the article you cite. The most balanced assessment of the Giles vs Picketty dispute is perhaps the piece Inequality: A Piketty problem? from The Economist.

Re:Is this worth reading? Answer: NO

[QuantumLeaper]

Thanks for proving how DUMB you are but using a dum word.

Re:does it matter?

[DarrylKegger]

It'd be helpful if you could actually state what you believe Piketty's flawed assumptions are.

Re:Spreadsheets as a software development platform

[preaction]

Yes and no.

No: Spreadsheets are useful as a kind of financial IDE, as long as the result is fed through an analyst and ends up at a programmer.

Yes: The result often does not end up at a programmer.

No, the real reason we're fucked is and always will be human greed.

CSV escaping

[tepples]

I'm assuming that you're trying to imply that the reason you can't export formulas in a CSV is that formulas will themselves contain commas. But CSV has a de facto standard way of escaping any field that has a comma. First, a CSV writer doubles up any quotation marks within the field, and then it wraps the field with a pair of quotes. Commas in such a quoted field are thus unambiguously part of the value, not field separators.

Example of a row with four elements, one of them containing a comma:

Milo,hello.c,"hello, world",1

Example of a row with three elements, one of them containing quotation marks:

Staisy,"Waiting for ""Superman""",2014-05-28

If that's not what you meant, could you explain further?

Re:CSV escaping

[Sarten-X]

Usually the purpose of a CSV is to have just values, not the function producing those values, so formulas are evaluated before saving.

I seem to recall some magical way to force Excel to save out formulas, but I don't remember it presently.

This is not a new observation

[khb]

http://panko.shidler.hawaii.ed... there's academic work going back years "indicting" spreadsheets. Not that anyone pays attention... inside academic circles or outside.

Bull! I'd love to debug that!

[TrollstonButterbeans]

That is a load of bull!

Debugging that would be child's play --- rename some cell ranges to give them names that that could be debugged thoroughly in 6 hours or less.

A similar program in a sloppy programming language would be infinitely obfuscated in function calls.

But frankly, this entire article is stupid. A spreadsheet is great for prototyping and if the shoe fits, then wear it.

The above formulas are child's play compared to nest javascript turdballs or even a lot of C++ I have seen written or common PHP.

Needless to say, several casuals have been tricked or dupped into thinking those formulas would be hard to debug and modded as such. And probably by the non-programmers that visit here that can be fooled by anything, the real veterans would quickly recognize how to de-spaghetti that "code" in minutes.

And how trivial compared to even some .bat and bash files I've seen.

Why do spreadsheets use a grid?

[grumbel5969]

While on the topic, why do spreadsheets use a plain grid in the first place? Why don't they use database-like tables with labled columns? The way spreadsheets mix data and presentation never made sense to me. It's like doing programming with raw memory addresses, instead of variable names and there is little you can do to structure and query the data properly. And meanwhile coming from the other side, the grid really sucks for doing layout of the data, as you are forced to use the same column and row spacing across the whole grid, even so most data actually on the grid in non-uniform.

yes for both

[globaljustin]

Is it quite as basic to be able to view multiple formulas in multiple cells?

yes, quite so

it requires higher level statistics knowledge and years of training to **interpret** the formulas

there's no getting around it...TFA is ridiculous

Spreadsheets should be used for the correct task

[Anonymous Coward]

The problem is when morons start using spreadsheets to do tasks that are better served with the correct tool.

Tools only ASSIST

[Anonymous Coward]

The "it's not the tool, it's the people" argument has one major flaw.

Tools are built so that people can perform tasks they can't otherwise do. As a result, if tool fails because it's not good enough for the task, at least part of the blame lies with tool and its creator.

No tools are built to ASSIST someone in performing a job. They aren't built to perform the job. All tools can be misused and mishandled.

A great example is a car as a tool. No one would argue that the car is a bad tool....until a drunk or a madman gets behind the wheel and ploughs into a class of pre-schoolers.

Re:Tools only ASSIST

[Luckyo]

We are not talking about MISUSE which is what you are talking about. We are talking about intended users attempting to use the car in the intended way and failing.

If you must have a car analogy, it would be a car that would not have enough power to climb hills in the region it's sold in.

That's why we already have SQL stupid!

[ajyand]

It would be stupid to continue with Spreadsheet and not realizing that SQL was a better choice.

worse ...

[cascadingstylesheet]

It;s a worse problem that Piketty wanted a certain conclusion, and a segment of the world wanted that same conclusion so badly that it accepted his drivel.

obvious

[Tom]

That needs explanation?

The only people I know who do serious business stuff with spreadsheets are the kind of middle management airbags who drag the company down in many other ways as well. You all know them, the types where work goes better in their department when they're on holiday or ill.

I mean, seriously? Anyone with an above room temperature IQ here who thinks spreadsheets are more than hacks for getting a quick, rough estimate of your data?

Re:Spreadsheets as a software development platform

[Tom]

Is that the industry that cratered a couple years ago, taking much of our economy with them?

I don't think they need telling. They need mercy killing.

Spreadsheets can become complex

[ponos]

The point is that spreadsheets can become very complex and they don't have any serious provisions for code review and debugging. Spreadsheets are not meant to be numerically stable and bug-free. They are meant for presenting and manipulating simple data. People abuse spreadsheets for database work and data transformation that are best done in a combination of SQL + something like R. I have been confronted with big spreadsheets in my research activities and the first thing I do is just convert the raw data in CSV, load it in SQL or R and take it from there, writing proper code in the process.l

Spreadsheets are very useful to me

[Ginger Unicorn]

I find spreadsheets are useful as a data entry UI.

I have to use Sage for my company accounts, and I find that the UI for Sage is so clunky and restrictive that it's easier to collate all the relevant data in LibreOffice, then run some Python scripts I wrote to process the .odt file to convert the information into CSVs full of payment data and balanced journal data that Sage can import

It's way easier to scrutinise all the figures in LibreOffice Calc and make sure it all reconciles and balances then import to Sage than the teeth-pulling agony of trying to get Sage to be at all helpful.

The same goes for any situation where i need to compile a long list of things that is handy to reorder / sum values. It's easier to use a spreadsheet as a data entry UI and run a simple script to validate/dump into a database than build a GUI myself for each task.

Doesn't know how to use reading properly

[Martin S.]

"What he's really saying is somebody else don't know how to use spread sheets properly."

Fixed that for you!

Example doesn't support the claim...

[evilviper]

there are transcription errors from the original sources and incorrect formulas.

Just which software would you suggest, as an alternative to spreadsheets, that works great when you mis-type the data you're analyzing?

Re:Spreadsheets as a software development platform

[internerdj]

If you work in a place where you have the time/resources for access to the ideal tools to solve every problem that pops up, I'd like to know where to send my resume.

OT: about your sig

[Will.Woodhull]

--

"If you learn Ubuntu, you know Ubuntu; if you learn Slackware, you know Unix"

....Okay....

And how exactly is Unix relevant to a lifeform that is part of a Linux ecosystem? Unix has as much relevance to me today as the DOS I outgrew three decades ago. While I know that much of what I work with in Ubuntu and Linux in general is descended from Unix, I also know that much of the English I communicate with is descended from ancient Elizabethan dialects that only classical thespians use any more. And even they don't use the old words when ordering fries at the local MickeyDees when on break between rehearsals.

Re:OT: about your sig

[phantomfive]

"Those who don't understand Unix are condemned to reinvent it, poorly."

Re:OT: about your sig

[Will.Woodhull]

Your POV is noted. And you get a higher grade for consistency.

Perhaps, though, you should expand your horizons. You can continue to spend your limited brain power on memorizing all those nifty little CLI commands with all their single letter modifiers. I stepped away from all that years ago: give me a GUI with mouse navigation and context sensitive menus and I can get more done more quickly and with less study of crap that I will not need to use again for maybe a year or so.

Re:OT: about your sig

[phantomfive]

Perhaps, though, you should expand your horizons.

Lol, you think I haven't used a mouse and a GUI? Or you think that Slackware doesn't support a mouse and a GUI?

Re:OT: about your sig

[Will.Woodhull]

Lol, you think I haven't used a mouse and a GUI? Or you think that Slackware doesn't support a mouse and a GUI?

Hmm. I assumed you were doing mostly CLI or you would not have made that claim in your sig. Unless you have been studying and using the CLI, your Slackware experience has given you no more knowledge of Unix than the Ubuntu experience. For that matter, anyone who wanted to get their feet wet in Unix could learn to use the Ubuntu CLI extensively, muck about in bash and seashells, and go nuts with vi or whatever the other one is (I forget-- it's a bad memory from decades ago, worse even than vi.)

So what is it about Slackware that makes you think it is somehow closer to venerable Unix than Ubuntu? The lack of extensive repositories? The need to spend hours chasing down dependencies and adjusting make criteria to compile something that has been compiled hundreds of times before?

BTW, you have lost all those extra points that were awarded earlier for consistency. "A foolish consistency is the hobgoblin of a lesser mind."

Re:OT: about your sig

[phantomfive]

For that matter, anyone who wanted to get their feet wet in Unix could learn to use the Ubuntu CLI extensively, muck about in bash and seashells, and go nuts with vi or whatever the other one is

Well yeah, that's the point, if you start mucking around under the covers, Ubuntu is kind of a mess. They get away with it because of apt-get. Slackware on the other hand is logical and clear under the covers, following the Unix philosophy.

Spreadsheet error rate studies

[StatFiend]

Googling turned up this paper which has a table of studies of speadsheet error rates:

http://panko.shidler.hawaii.edu/SSR/Mypapers/whatknow.htm

Re:Spreadsheet error rate studies

[Tom]

Thanks!

This

[DarthVain]

Excel is terrible at this. It wants to *help* and makes all sorts of assumptions. Even when you change to to what it should be, it will change it to something else and be all like "Hey wasn't I helpful for you!". Which can be defeated by some weird workaround you can find on the internet that you can never remember when it inevitably comes up again.

Most database ID keys are stored as text, even though they are literally a "string" of numbers. As are a lot of other unique keys that are used. Excel loves to put that into fantastic scientific notation for you as they are really big numbers and that is what you want right? Or into Long. Or Double. Format. Re-format. Copy values. Smash face against monitor. Set Format. Import. Cry. Search Internets. Eventually figure it out again. Swear. Curse whoever gave you stupid data in the first place... Why are people sending you data in excel in the first place, in such a horrible way? Face Palm.

If someone is having a problem with excel, or with access after importing data, I would guess about 90% of it is excel's messed up formatting/Typing.

Re:Spreadsheets as a software development platform

[Virtucon]

ahh the old "when all you have is a hammer, every problem looks like a nail." issue huh? You need to ask yourself if something can be prototyped in Excel, that's fine. There are tools out there, open source if you have zero budget. If you have to prototype out a solution or create a solution that's robust, that can be documented and reviewed, it's more often easier to use something like "R".

While you can get easy answers from a spreadsheet you may not necessarily get the right answer and that's where their abuse becomes apparent. I've seen railroads actually use Excel to manage daily consist for trains and when they started using it was for an emergency, but then every day suddenly had an emergency/exception and then it became operationally necessary. That was until the guy who based his whole job/career around maintaining the spreadsheet finally retired, then it was WTF? for the rest of the operation because nobody could make sense out of it.

Good Enough Era

[ntime60]

This falls well within Microsoft's "Good Enough" computing strategy of software design. Sounds like to me someone needs a new line of work if he/she is tired of supporting the Good Enough crowd. The Good Enough era is also why M$ shares are declining...there is not enough good.

Ordinary source code includes formulas

[tepples]

How can you possibly expect a CSV to contain a formula? It's a text file.

My Python programs are text files, and they contain plenty of expressions (or "formulas" in spreadsheet parlance). So do the JavaScript programs that add behaviors to Slashdot and other web sites. So I still don't see how a mode to export a worksheet's formulas as delimited text would be so impractical.

just floating-point errors...

[david_bonn]

A lot of spreadsheets have formulas that are dependent on calculated values from the preceding row or column. In fact, the replication functions most spreadsheets have encourage you to do this.

The problem, as any well-trained computer scientist knows, is that floating-point errors can rapidly accumulate using these kinds of calculations. Very. Rapidly. That means that your answers fifty or eighty rows along might well be gibberish.

I've been to three separate meetings at three separate companies whee different people's spreadsheets gave hilariously differing answers. Faces got red, voices got raised. The reality was that no one had numbers that were even close to right.

Re:Spreadsheets as a software development platform

[CmdrTamale]

And why should the demons of the financial industry deserve mercy?

It's past time to cull.
--
If we'd listened to all the people who said it couldn't be done there'd be no fusion power stations or cities on the moon.

Subtext

[mynamestolen]

I see this as related to the Financial Times now discredited attempt to attack Piketty. Even the FT sister publication The Economist basically said FT tried to do hatchet job and failed. Check the wikipedia article before the corporations rewrite it:
https://en.wikipedia.org/wiki/...

Only true if you are using Excell

[AlabamaCajun]

I knew accountants that did many things with spreads that worked.
One ran many company ledgers on Lotus 123 including printing the balance sheets.
In another case one accountant ran a companies pricing on spreads even though there was a mainframe that could have done it. (Job Security). This too was Lotus spreads.
I will agree with the author that considering the bugs and problems with Excel, Access and Word speak volumes to that fact they are now just interim tools not permanent storage and retrieval.I tell people all the time Access and Excel are just work tools.

define "spreadsheet" in your terms

[globaljustin]

yeah...obviously you (and whoever upmodded you) have some sort of database branded software you use

what is it?

SPSS?

what are the options? what database software?

tell me then I'll explain why whatever that software does can be done on an excell spreadsheet, with all work being shown for anyone with a bachelor's able to follow easily

it's done all the time...it's math...

so tell me, what is a "non spreadsheet" software program that analyzes data

do it, so I can explain how it doesn't matter for you...this is a bullshit TFA with a pointless argument and it bothers me so many agree with it

Piketty's right already_look where your taxes go

[globaljustin]

Piketty's data is sound. His conclusions support his contextualiztion of the problem in a global context.

I'm not paying a global tax, but our nation pays money to all kinds of countries in the form of aid & econmic benefit...that's tax money...hell, the US practically armed the Egyptian army

that's our tax money...so we're already paying taxes for world peace as it stands

so you admit what no one else who criticizes me will...that your criticizm is based in your political ideology not at all about actual usage of spreadsheets in research work

Pareto principle

[NewYork]

https://en.m.wikipedia.org/wik...

The horror!

[Fuzzums]

Too many times a researches made a fancy spread sheet, added some stuff over the years and then wants a decent application that does the same.
Many times the problems they're working on are very interesting, but it's a BIG challenge to rewrite everything to a normal application :)
But of course we like challenges like that :)

Re:Piketty's right already_look where your taxes g

[Attila Dimedici]

I have not checked Piketty's data myself, but I have seen it criticized by people who agree with his conclusions. I have also seen his comeback to a criticism that went into detail about the mistakes in his calculations. That response was essentially, "No, you are wrong." Piketty did not actually address the specific problems pointed out in his work.

Maybe a SpreadSheet Definition Language?

[tkotz]

I'm imagining something closer to VHDL than a traditional programming language where you primarily define connections and initial values.
It needs a way to name cells, groups of cells, contents and a way to set cell contents.
'alias' <identifier> [ '(' <parameter list> ')'] '{' <value> '}'
'set' <cell range> '{' <value> '}'
You really just need these two mechanisms, plus some extensions for formatting. A way to have set support relative addressing in the value would be nice.

# Named Cells
set A1 {"AGE}
alias age {B1}
set A2 {"Year of Birth}
alias birth_year {B2}
set A3 {"Current Year}
alias current_year {B3}

# Named Routines
alias SUBTRACT(minuend, subtrahend) {(minuend &#8722; subtrahend)}

# Simple Logic
set birth_year {1980}
set current_year {2020}
set age {=SUBTRACT(current_year, birth_year)}

# Relative addressing idea
set C1 {0}
# Previous row
set C2:C100 {=@[-1]+1)}
# Previous column
set D1:Z1 {=@[-A]*2}
# Diagonal
set D2:Z100 {=@[-A,-1]+3}

It is sort of like a language that has manual linking specification with the concurrency of VHDL. This would at least be able to be code reviewed. Redundant sets of the same cell or alias would probably be an error. That would allow for creating a dependency tree. That makes the ordering of the statements unimportant. maybe it requires aliases to be defined before use.  It wouldn't be hard to write a script that makes a spread sheet from this, but what would be better would be to add a spreadsheet plugin that lets you view this code side by side in realtime with the spreadsheet. Automatically having changes update across. and letting you use the aliases directly.

Hmm... I need to write some yacc/bison.

Re:Where's APK?

[Tom]

Ignoring trolls does work, though some of them are so persistant that I see my mental disorder theory strongly verified.

Re:Piketty's real problem isn't spreadsheet-relate

[Capsaicin]

The Economist essentially says: "We can't be sure he intentionally fudged the numbers, and he says he didn't, so we'll take his word for it."

I don't think that's anywhere near a sufficient summary of the writer at The Economist says, no.

That is an extremely weak defense.

You have committed a very serious error of thought. Choose your presumptions with greater care!

Before you go accusing anyone of murder, rape or serious academic misconduct you had better have some real evidence to back it up. I trust you are not claiming that Giles' points of attack are sufficient to establish so serious an allegation to any reasonable level of proof?

Be sceptical about Picketty's treatment and use of data, of course, but there can be no serious suggestion that Picketty "intentionally fudged the numbers," and it is scurrilous to imply he may have. Yes, we ought certainly "take his word for it." To do otherwise, absent compelling evidence to the contrary, is beyond contempt. Seriously!

Besides, that piece was hardly a defence, but rather a fairly neutral assessment of the dispute. For a more spirited, and highly informative, defence, today's data analysis piece in the The Guardian is more in order.

Re:Piketty's real problem isn't spreadsheet-relate

[Entrope]

Someone who accuses another of committing "a very serious error of thought" should probably not compare academic misconduct to murder or rape. When was the last time anyone went to jail for academic misconduct?

Does Piketty's book disclose that he altered the numbers he worked from, such that they do not reflect the sources he cited? If not, that would constitute serious academic misconduct under some (relevant) standards.

Of course, he admits to altering his data after someone else noticed it, but some of his changes are apparently plain errors, and the rest are not annotated or explained. They also push the data in a direction that Piketty finds useful (long before the FT article came out, scholars criticized the book for making overblown conclusions that were not well-supported by the data it presents), which is further reason to be skeptical about the nature of the changes. Data manipulation doesn't have to be intentionally biased in order to be consistently biased.

To pick just one flaw in your new link, I cannot reproduce the 6% discrepancy that Reed claims in his The Guardian piece between data sources (b) and (c) -- I calculate it as an average 3.8% difference in the top 10%'s wealth over the eight-year overlap, dropping to 3.3% if you exclude 1974's anomalously large discrepancy, and in either case dropping a bit if you round (b) to whole percentages to match (c). On top of that, the difference goes in the other direction for the top 1% estimates, which makes suspect claims of systematic bias between the two, rather than sampling noise. I think I'll opt out of being "highly inform[ed]" by bad arithmetic, thank you very much.

Lotus Improv Models vs Spreadsheets

[Keybounce]

Many years ago, there was a program on the Pizza Box (aka NeXT machine) called Lotus Improv. It even came out on early windows systems.

It did something wonderful to spreadsheets.
It moved the formulas out of the cells, into a formula plane.
It got rid of the 2d system, where you put different sections of data on different parts of the plane, and try to keep things straight as your database grows, and instead used a collection of n-d spaces. Each one of the collections dealt with one subject; each one could track as many as 8 dimensions, and tracking 3-5 was typical.

It worked. It worked well.

Code was readable -- nothing was duplicated in every cell, or rather, almost duplicated with slight variations in each cell that you had to hope and pray was given the same and correct slight alteration each time (and god forbid you needed to change the template spread across everything). Instead, you defined clear statements once, and it automatically adjusted for each different cell.

Improv was wonderful.

Why did it die?

Re:Piketty's real problem isn't spreadsheet-relate

[Capsaicin]

Someone who accuses another of committing "a very serious error of thought" should probably not compare academic misconduct to murder or rape. When was the last time anyone went to jail for academic misconduct?

Whether an offence carries the prospect of a custodial sentence or is immaterial to the question of the presumption of innocence. You err again. However your previous error, to work instead upon the presumption of guilt, was by far the more serious. This you must not do!

The "comparison" (and there was no actual comparison) with murder and rape was to emphasise, to someone who would so easily accept an accusation that "data was pulled out of thin air," the seriousness of that allegation. There can be no more serious accusation levelled against a scholar qua scholar than academic fraud. One might not end up in prison, but such an allegation being proved, rightly spells an end to an academic career and perhaps even an ancillary career (Consider the well deserved fate of one Andrew Wakefield for instance). It is an extremely serious matter.

Before you go accusing anyone of murder, rape or serious academic misconduct you had better have some real evidence to back it up.

Does Piketty's book disclose that he altered the numbers he worked from ...

Having not read the book I would presume that he it did. As should you. Moreover the fact that he discloses his data and sources exposes any alterations to scrutiny (including that of Giles). I agree that he ought in addition be explicit about the need and methodology of adjustments made to raw data and should be disappointed if he has not.

To pick just one flaw in your new link ...

I claim no ownership of that link, and really shouldn't respond to this distraction (take it up with Howard Reed) ... however ...

I cannot reproduce the 6% discrepancy that Reed claims in his The Guardian piece between data sources (b) and (c) -- I calculate it as an average 3.8% difference in the top 10%'s wealth over the eight-year overlap

On the ten (10) year overlap, (the last eight (8) of which are contiguous), I find an average 5.4% discrepancy. On the last 8 (contiguous) years the discrepancy is 6.2%. I'm not sure what it is you are doing to get your results, are you working from the supplied spreadsheet?

I think I'll opt out of being "highly inform[ed]" by bad arithmetic, thank you very much.

Quite.

Re:Piketty's real problem isn't spreadsheet-relate

[Entrope]

News flash for you from the real world: Society uses different standards of proof in criminal cases than for academic misconduct investigations. Still, I never suggested we should presume that Piketty was guilty of either, and I think you are arguing in bad faith to suggest I did.

We could argue all day about whether it is better to use the original Series C numbers (as I did) or the revised Series C numbers (as Reed did, perhaps because every revision for the top 10% of wealth was a downward one). We could also argue whether the differences between data sets are because of differences in how they estimate "wealth inequality" or whether they are instead estimating different kinds of wealth inequality (Atkinson et al. used estate numbers, which quantifies things at the end of life, which is about the least relevant quantity for Piketty's proposed policies).

At any rate, none of the discrepancies are pertinent to Piketty's claim that wealth inequality has been increasing since 1980. As the same data set is available and used for the 1976-2005 period, inter-data-set differences should not affect the shape of the curve during that period -- only its level. It is also tendentious to assume, as Reed and Piketty apparently want us to, that lower wealth-inequality estimates from during and after the recent global recession mean that the measurements need to be adjusted, rather than that they reflect an actual change in the wealth distribution.

Re:Piketty's real problem isn't spreadsheet-relate

[Capsaicin]

We could argue all day about ...

But we shan't. As I said it's a distraction to the point I made, take it up with Mr Reed.

News flash for you from the real world: Society uses different standards of proof in criminal cases than for academic misconduct investigations.

You are confused. I wrote nothing about standards of proof. It is yet another error to conflate presumptions with a standards of proof. Third strike ... you're out.

Re:Piketty's real problem isn't spreadsheet-relate

[Entrope]

If I am confused, it is because you are spewing nothing but stuff and nonsense, and because you continue to lie about "presumptions" that only you have suggested. You admit that academic misconduct is not a crime like the things you compared it to, and you implicitly concede that it is not judged by the same standards. There are a lot of things that can ruin a career, and some of them are well deserved! At least in the US, we are very tired of people who feign outrage when they cannot otherwise defend their positions, so do not expect to get away with that here.

Re:Piketty's real problem isn't spreadsheet-relate

[Capsaicin]

You're out.