Beta
×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

Why You Shouldn't Use Spreadsheets For Important Work

Soulskill posted about 5 months ago | from the they'll-throw-you-in-a-cell dept.

Math 422

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?'"

Sorry! There are no comments related to the filter you selected.

What he's really saying is (4, Insightful)

Anonymous Coward | about 5 months ago | (#47103557)

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

Re:What he's really saying is (5, Insightful)

Anonymous Coward | about 5 months ago | (#47103571)

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

Re:What he's really saying is (0, Flamebait)

Anonymous Coward | about 5 months ago | (#47103633)

To be fair, neither do the vast majority of people who use spreadsheets for paid work, but they still believe they're better than you because they get paid to do it.

FTFY

Re:What he's really saying is (-1)

Anonymous Coward | about 5 months ago | (#47103701)

To be fair, neither do the vast majority of people who use spreadsheets for paid work, but they still believe they're better than you because they get paid to do it, because, ultimately, frogskins are the only metric that matters.

You missed some.

Re:What he's really saying is (-1)

Anonymous Coward | about 5 months ago | (#47103953)

To be fair, neither do the vast majority of people who use spreadsheets for paid work, but they still believe they're better than you because they get paid to do it, because, ultimately, foreskins are the only metric that matters.

You missed some.

FTFY.

Re:What he's really saying is (5, Insightful)

Anonymous Coward | about 5 months ago | (#47103629)

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 (5, Funny)

Tyler Durden (136036) | about 5 months ago | (#47103707)

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

Re:What he's really saying is (2)

AK Marc (707885) | about 5 months ago | (#47103785)

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

Re:What he's really saying is (3, Insightful)

geekoid (135745) | about 5 months ago | (#47103797)

It needs restating because people forget it all the time.

Some things stick (5, Informative)

TapeCutter (624760) | about 5 months ago | (#47103927)

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 (5, Funny)

viperidaenz (2515578) | about 5 months ago | (#47103941)

Were the survey results collated on a spreadsheet?

Re:Some things stick (4, Funny)

peragrin (659227) | about 5 months ago | (#47103955)

nope Access Database.

now which is scarier?

Piketty's real problem isn't spreadsheet-related (-1, Redundant)

Anonymous Coward | about 5 months ago | (#47104059)

Thomas Piketty's economic data 'came out of thin air' [theguardian.com]

The Financial Times has suggested that Piketty's work contains a series of errors that appear to fatally undermine large parts of his thesis. The normally restrained paper claims that some of the data Piketty uses to support his arguments about yawning inequality in Britain and Europe are dubious or inexplicable. Some of this, the paper suggests, may be down to straightforward transcription errors. More damningly, the FT claims, "some numbers appear simply to be constructed out of thin air"

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

Yeah, but if The Guardian isn't good enough, how about Mother Jones?

Chris Giles Challenges Thomas Piketty's Data Analysis [motherjones.com]

Chris Giles of the Financial Times has been diving into the source data that underlies Thomas Piketty's Capital in the 21st Century, and he says he's found some problems. The details are here. Piketty's response is here.

Is Giles right? Experts will have to weigh in on this. But Giles' objections are mostly to the data regarding increases in wealth inequality over the past few decades, and the funny thing is that even Piketty never claims that this has changed dramatically.

Wut? Per MOTHER JONES, Piketty says wealth inequality HAS NOT BEEN INCREASING?!?!

Re:Piketty's real problem isn't spreadsheet-relate (3, Informative)

Anonymous Coward | about 5 months ago | (#47104159)

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: What he's really saying is (4, Insightful)

Anonymous Coward | about 5 months ago | (#47104107)

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

Re:What he's really saying is (4, Insightful)

jd2112 (1535857) | about 5 months ago | (#47104171)

"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 (3, Insightful)

jythie (914043) | about 5 months ago | (#47104201)

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.

Idiocy (0)

Anonymous Coward | about 5 months ago | (#47103559)

Both methods are useful. In fact, concurrence of the two computational methods would be a good check.

open source your sheet (0)

Anonymous Coward | about 5 months ago | (#47103573)

The code is hidden away in dozens if not hundreds of little cells

Sounds like spreadsheet software needs an export feature that dumps all the formulas into a file in human readable format. And maybe it already does, I wouldn't know because I never, ever, ever use spreadsheets.

OOXML or Excelception (1)

tepples (727027) | about 5 months ago | (#47103671)

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 [j-walk.com] .

Re:OOXML or Excelception (1)

smitty_one_each (243267) | about 5 months ago | (#47103731)

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

Re:OOXML or Excelception (0)

AK Marc (707885) | about 5 months ago | (#47103893)

You can also just save it in CSV, and that will separate it out like you want. In plain text. But in a spreadsheet, it's possible to lose track. A single error can go undetected because you can't see the equation within the program. Yes, I've used equations to long that I had to copy them to notepad to be able to read them. You can scroll the function bar, but it's impossible to see the whole thing at one time in the original format. Though, it's quite easy to write the equation in notepad and copy it into a cell. You can even format it to line up parenthesis and make it easier to read/edit.

Re:OOXML or Excelception (1)

tepples (727027) | about 5 months ago | (#47103933)

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 (1)

viperidaenz (2515578) | about 5 months ago | (#47103965)

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:open source your sheet (2)

MightyYar (622222) | about 5 months ago | (#47103885)

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.

Re:open source your sheet (1)

sjames (1099) | about 5 months ago | (#47103943)

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

Spreadsheets - best and worst thing there is (3, Funny)

BeerCat (685972) | about 5 months ago | (#47103593)

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 (1)

Anonymous Coward | about 5 months ago | (#47103719)

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 (0)

Anonymous Coward | about 5 months ago | (#47104133)

Dude, I feel your pain. My boss refuses to let me build my own tools as he feels I waste too much time (I still do, of course - otherwise I'd never get anything done! - I just don't tell him about it. Then a week later after he's been wrestling with Excel trying to get it to split-out and colour-format 900k rows he comes and asks me to help, then doesn't like what I have to tell him, which is invariably that the way he's approaching the problem is half-arsed and will eventually fail (which it is, by that point).
His response is invariably that he knows that stuff like "arrays" and "functions" might be the "technically nicer" way to do things (where I've "quoted", he's using actual real-life air-quotes) but for the moment he needs it to just work, so I need to use a separate spreadsheet to work with the data that the code is manipulating.

I really like the guy outside of work, he's a great guy, but he's a complete and utter cunt to work underneath.

Re:Spreadsheets - best and worst thing there is (5, Informative)

plover (150551) | about 5 months ago | (#47103835)

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 (2)

Bonobo_Unknown (925651) | about 5 months ago | (#47104197)

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.

Sounds like job security. (2)

Rinikusu (28164) | about 5 months ago | (#47103615)

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. (2)

mjwx (966435) | about 5 months ago | (#47104013)

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.

audit (0)

Anonymous Coward | about 5 months ago | (#47103623)

It might be helpful is spreadsheets had built in tools for auditing. Quattro had a very simple one.

Is this worth reading? Answer: NO (0)

Anonymous Coward | about 5 months ago | (#47103627)

What a waste! As any sane person knows that all work that is worth doing is done written in assembler and that any other opinion is simply dum as a Democrat.

Re:Is this worth reading? Answer: NO (1)

epyT-R (613989) | about 5 months ago | (#47103661)

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

So what's the alternative? (5, Interesting)

Cyberax (705495) | about 5 months ago | (#47103631)

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? (1)

DigiShaman (671371) | about 5 months ago | (#47103685)

File Maker Pro.

(trying not to laugh)

Re:So what's the alternative? (2)

simonbp (412489) | about 5 months ago | (#47103715)

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

Re:So what's the alternative? (2)

rubycodez (864176) | about 5 months ago | (#47103735)

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

Re:So what's the alternative? (1)

Cyberax (705495) | about 5 months ago | (#47103763)

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? (1)

mbkennel (97636) | about 5 months ago | (#47103821)


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? (2)

Cyberax (705495) | about 5 months ago | (#47103851)

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? (0)

Anonymous Coward | about 5 months ago | (#47104203)

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

The same can be said for Matlab, Mathematica, and the not mentioned Mathworks. The difference is in accuracy, where a real math program does very well and Excel has the most issues with complex formula.

Re:So what's the alternative? (1)

sjames (1099) | about 5 months ago | (#47104085)

That's what the GNUplot module is for.

Re:So what's the alternative? (2)

geekoid (135745) | about 5 months ago | (#47103745)

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

Re:So what's the alternative? (1)

by (1706743) (1706744) | about 5 months ago | (#47103767)

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? (1)

Balthisar (649688) | about 5 months ago | (#47103957)

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? (0)

Anonymous Coward | about 5 months ago | (#47103959)

Access/Base?

Re:So what's the alternative? (1)

Breakerofthings (321914) | about 5 months ago | (#47104067)

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? (1)

murdocj (543661) | about 5 months ago | (#47104175)

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

Re:So what's the alternative? (1)

hydrodog (1154181) | about 5 months ago | (#47104125)

It's called Octave my friend...

Spreadsheets shouldn't have code (0)

Anonymous Coward | about 5 months ago | (#47103647)

The fundamental problem with using spreadsheets is putting code in them. They are for doing math, not making tables or pretty forms.

This is like saying first class is the only way to (1)

SpaceCadetTrav (641261) | about 5 months ago | (#47103657)

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

Fond memories of spreadsheets. (0)

Anonymous Coward | about 5 months ago | (#47103665)

I like spreadsheets enough for quick and dirty work. My boss has used them for serious work. He wanted me to port the logic and computations from one spreadsheet with dozens of operations to our code base, and I figured it was less work to write a spreadsheet-to-code generator. It turns out I was right, with how many significant changes he wanted to make to the original spreadsheet.
Also, the computation needed to be fast: simply using a generic excel library to populate and execute the computations was a couple orders of magnitude slower.

Article suggests a corresponding "why you should" (0)

Anonymous Coward | about 5 months ago | (#47103667)

The summary suggests to me a corresponding "why you should". I'm not thinking "use spreadsheets" though, and I'm not thinking of a "you" who is the typical user.

I'm thinking "find a betterway to map the code to the spreadsheets" and the "you" is somebody with the capability to do that. It might be the developers of the spreadsheets coming up with a good way to dump code from the sheet, or it might be a language designer working on this problem from the other side.

This isn't a problem I've studied much, but the spreadsheet seems like somewhat of a presentation layer. No sane programmer would have hundreds of variables named C1...C666. They might use a foreach or map function to iterate over some variable, and then display it in column C.

I bet that for most sheets, it might not be too hard to dump code. In fact, if it's hard to dump code that might be a sign that you're pushing sheets beyond practicality.

"spreadsheets" = computation program (2, Insightful)

globaljustin (574257) | about 5 months ago | (#47103673)

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 (4, Insightful)

geekoid (135745) | about 5 months ago | (#47103769)

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.

can or cannot compute (1)

globaljustin (574257) | about 5 months ago | (#47103981)

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 (1)

tepples (727027) | about 5 months ago | (#47104055)

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.

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

globaljustin (574257) | about 5 months ago | (#47104155)

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:"spreadsheets" = computation program (1)

murdocj (543661) | about 5 months ago | (#47104185)

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?

I agree... (1)

MatthiasF (1853064) | about 5 months ago | (#47103815)

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.

Not "important work" (3, Insightful)

LordLucless (582312) | about 5 months ago | (#47103703)

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" (1)

tepples (727027) | about 5 months ago | (#47104065)

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

Code reviewing a spreadsheet (4, Insightful)

muhula (621678) | about 5 months ago | (#47103711)

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 (1)

JustNiz (692889) | about 5 months ago | (#47103723)

>> 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 (4, Insightful)

geekoid (135745) | about 5 months ago | (#47103783)

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

Easy to list formulas in spreadsheet for review (0)

Anonymous Coward | about 5 months ago | (#47103961)

http://blog.contextures.com/archives/2012/09/27/list-all-formulas-in-workbook/
http://spreadsheetpage.com/index.php/tip/creating_a_list_of_formulas/

Would be even better if you could NAME a cell.

And look you can name cells!!! (0)

Anonymous Coward | about 5 months ago | (#47103973)

http://www.computerhope.com/issues/ch000704.htm

Same AC. Should have searched the web a little more before posting. So should the original author. Instead of rambling about a problem as if it were intractable, how about finding a solution for it!?

View from Silicon Valley (0)

Anonymous Coward | about 5 months ago | (#47103733)

Many engineers driving Moore's law on ICs, storage, & communications use spread sheets. Not to say there isn't a fair amount of custom code. But the only free alternative appears to be Python. I expect all non-open source language suites will soon change a couple $k/year subscriptions.

Another major issues with spreadsheets (3, Insightful)

Jonathan Mann (3481921) | about 5 months ago | (#47103739)

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? (4, Insightful)

Virtucon (127420) | about 5 months ago | (#47103741)

You're doing it wrong.

Re:Spreadsheets as a software development platform (4, Insightful)

preaction (1526109) | about 5 months ago | (#47103865)

Tell that to the entire finance and insurance industry.

Re:Spreadsheets as a software development platform (3, Funny)

Virtucon (127420) | about 5 months ago | (#47104007)

is that why we're fucked?

Excel is not a programming language (1)

Anonymous Coward | about 5 months ago | (#47103755)

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

Did you know (0)

Anonymous Coward | about 5 months ago | (#47103795)

that 1-2-3- was touted as a database? It's true. People did mailing lists with it, for example.

Piketty's work will be done for him (5, Insightful)

matbury (3458347) | about 5 months ago | (#47103801)

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 (0)

geekoid (135745) | about 5 months ago | (#47103849)

A) With any work, having colleagues try to tear it apart is a good thing. And one little decimal can be expensive.

I don't know why 'neo-libral' economist would attack him.

"When you hear of character attacks against Piketty or some other diversionary tactic, you'll know his data is correct."
Logic fail.

YOU can have character attacks AND the data can also be wrong. "That dumb ass didn't put the decimal in the right place" for one example

Piketty's work will be done for him (0, Insightful)

Anonymous Coward | about 5 months ago | (#47104031)

There's already tons of documentation of Piketty's many mistakes. And his "mistakes" all seem to support his thesis, which is a pretty big pill to swallow.

http://blogs.ft.com/money-supply/2014/05/23/data-problems-with-capital-in-the-21st-century/?Authorised=false

His bigger issue is that his data does not support his eventual conclusion that a wealth tax is necessary. It's a big jump from his data to that extreme solution.

My biggest issue is this: If R > G really trumped individual effort, why are the three richest men in the world (Gates, Buffett, Slim) all self made billionaires? If R > G was such a big deal, I would expect the richest person in the world to be a Rockefeller, or an heir from one of the other 19th century robber barons. Returns on investment have outpaced economic growth for a long time. It's clear that this is not the factor that Piketty makes it out to be. It's one thing to say that more and more will go to the top 1%. 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?

Re:Piketty's work will be done for him (1)

tomhath (637240) | about 5 months ago | (#47104043)

Other economics papers that reached similar conclusions such as the well known Growth in a Time of Debt [theconversation.com] 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.

Excel needs a compiler add-on (0)

Anonymous Coward | about 5 months ago | (#47103817)

Spreadsheet programs emitting the models in a static verifier friendly format might be a good thing. Outputting human friendly programming language of choice as well would be virtuous.

New type of tool needed? (1)

mars-nl (2777323) | about 5 months ago | (#47103827)

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.

Says the coder who wants you to BUY his software (0)

Anonymous Coward | about 5 months ago | (#47103837)

sigh

You can't audit spreadsheets (3, Insightful)

swm (171547) | about 5 months ago | (#47103877)

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... (2)

Diddlbiker (1022703) | about 5 months ago | (#47103881)

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... (2)

Luckyo (1726890) | about 5 months ago | (#47103999)

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... (0)

Anonymous Coward | about 5 months ago | (#47104023)

Using the wrong tool is one of the first signs of being a bad producer.

Re:Blame the tool... (2)

J. J. Ramsey (658) | about 5 months ago | (#47104139)

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.

Misleading title (1)

gnu-sucks (561404) | about 5 months ago | (#47103913)

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.

Important work IS quick and dirty work (0)

Anonymous Coward | about 5 months ago | (#47103919)

I hate to break the news, but important work usually IS quick and dirty work. In any office setting I've been in (from physics research at university to a typical office drone sales environment and quite some more) time is the one thing that's really really scarce. When you try to figure out the numbers you need to know, using anything else usually means missing a deadline, missing the boat, drowning in the extra workload, or getting it wrong because you couldn't play around enough to see the pitfalls from the obvious calculation.

Thulsa Doom provides an alternative (-1)

Anonymous Coward | about 5 months ago | (#47103979)

My child, you have come to me my son. For who now is your father if it is not me? I am the well spring, from which you flow. When I am gone, you will have never been. What would your world be, without me? My son.
-Thulsa Doom

Get down on your fucking knees and pray for the mercy and sweet salvation of Thulsa Doom! You undeserving peasants are fortunate enough to be graced with his words let alone to enjoy his mercy. What have you done to be privileged enough to be considered as one of Thulsa Doom's children? Nothing.

Only Thulsa Doom can give you a purpose in this life. Only Thulsa Doom can give you meaning to your every breath. Without His existence you'd be like a slug: A useless life with no real meaning.

Infidel Defilers. They shall all drown in lakes of blood.
-Thulsa Doom

You deserve damnation. You deserve pain. Still Thulsa Doom may grace you and bring you into the light of lights and show you the way. Be joyous if he does but if he doesn't do not act like you deserve better. Your mortal life is as useless as dog shit to Thulsa Doom and you know this is the truth of life and of nature.

Why not serious? (0)

Anonymous Coward | about 5 months ago | (#47103983)

I think my wife, who works on $30-100million construction projects, would disagree with him...

hjomo (-1)

Anonymous Coward | about 5 months ago | (#47104001)

operating systems, revel in oUr gay MEGS OF RAM RUNS FUTURE. THE HAND and I probably Has brought upon Join in. It can be and I probably 'first post'

Dear "Computer science professor", (1)

Cammi (1956130) | about 5 months ago | (#47104011)

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 (5, Interesting)

turp182 (1020263) | about 5 months ago | (#47104051)

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=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")))))))))))

Re:A Formula only an Actuary could Love (0)

Anonymous Coward | about 5 months ago | (#47104105)

I think he forgot a right parentheses somewhere.

Understand their uses and limitations ... (2)

MacTO (1161105) | about 5 months ago | (#47104097)

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.

The second best tool (0)

Anonymous Coward | about 5 months ago | (#47104149)

...to do just about anything. There is always a better tool available but the ubiquity of spreadsheets makes them an attractive choice in many situations.

Spread'em Sheets (0)

Anonymous Coward | about 5 months ago | (#47104191)

what a bunch of useless sheets

Load More Comments
Slashdot Login

Need an Account?

Forgot your password?