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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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.

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

[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

[Boronx]

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

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?

[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]

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?

[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?

[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?

[Gumpu]

You use something like SAS or R.

"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.

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.

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

[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.

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.

Spreadsheets as a software development platform?

[Virtucon]

You're doing it wrong.

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

[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: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.

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.

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

[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

[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

[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:Spreadsheets as a software development platform

[Virtucon]

is that why we're fucked?

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

[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

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

[coolsnowmen]

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

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: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: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.