Is Microsoft Excel an Adequate Statistics Package?

http://www.practicalstats.com/xlsstats/excelstats.html

by Jerry2 • 10 years ago

78 102 10 years ago

kprybol2• 10 years ago

The other reason to not use Excel for stats? It's virtually impossible to reproduce your work unless you documented every step in some other format. Excel is easily one of the worst tools you can use if you ever need to refer back to/redo your original work at any point in the future or if you need to be able to validate your result with any confidence. Yea I know there are some people who are freaking magicians with VBA who can "fix" many of the problems listed, but that doesn't absolve excel of its statistical sins.

MlCROSOFT• 10 years ago

I think that's where R stats package (R Project) really shines, I love the command line console that displays every input / action so you have a track record of what you did. Used it for T-tests, linear regression, ANOVA, MANOVA. For those working in biological / environmental science fields, I would definitely recommend it.

okket2• 10 years ago

There is a free R course on Codeschool if anyone wants to try it for an hour or two. It's fun.

https://www.codeschool.com/courses/try-r

kprybol2• 10 years ago

Oh, almost forgot! (can't edit so I'll reply to myself) - there's no way for you to set a fixed seed using the built in random number generator. REALLY hope you don't need to reproduce your results because it's essentially impossible. Again, it's technically possible to implement your own Lehmer random number generator within Excel and rely on that and again VBA is always and option, but let's be realistic. The people who understand the need for setting fixed seeds, can implement their own random number generator, and/or are comfortable actually programming aren't going to using Excel anyway (willfully at least)

Nicholas_C2• 10 years ago

If you're ripping through the command line you can run into the same problem.

If someone is manipulating data in excel and erasing the original data they are not a good analyst. Further, it is very easy to add documentation of steps taken to files.

tangue2• 10 years ago

Considering Microsoft recent involvment with R I have the crazy hope that future versions of Excel will be shipped with R as a scripting language.

KhalilK• 10 years ago

There is an Excel addin (RExcel) that allows the use of R from within Excel. But it's proprietary.

blahi6• 10 years ago

RExcel blows.

https://bert-toolkit.com/

Mikeb85• 10 years ago

IMO adding scripting to a spreadsheet is a waste of time when R can simply import a spreadsheet, perform whatever operations needed, then spit out another spreadsheet/database/whatever format you want.

actuallyalys• 10 years ago

Plus, if you use a tool like RStudio or Jupyter, it's pretty easy to see the data as you manipulate it, which is a commonly cited advantage of Excel, with none of the awkwardness of trying to look at cell formulas.

blahi6• 10 years ago

That would depend on what you want to do. If by statistical analysis you mean regression or similar tasks, then sure R is the way to go.

However there are other family of analytics tasks which can be summed up as "decision analysis". Taking regression models (for example, maybe even built in R) and simulating them under different inputs, getting quantiles, sensitivity, etc. This goes much further with multiple sets, multiple outcomes with decision trees (NOT regression trees/CART) and even further with solvers. Excel is the best option in those tasks most of the time because of quick data entry and already built output reports and interfaces.

tangue2• 10 years ago

For people used to code, yes. But many excel user won't leave the ui they're familiar with. Including R will build a bridge between Excel user and modern development features (testing, vc ...)

dandermotj5• 10 years ago

So is Hadley Wickham... https://twitter.com/hadleywickham/status/748392441154248704

peatmoss2• 10 years ago

Well, later in that thread he did write:

"I am worried by the number of people that couldn’t tell that this was sarcasm"

chris_wot2• 10 years ago

How does LibreOffice compare?

gaius2• 10 years ago

It's just as unsuitable. But there are plenty of open source packages that R the right tool for the job...

nn3• 10 years ago

The article disagrees with you

""" Solution #2: Alternatives to Excel Yalta (ref 1) states that p-values [inverse probability distributions] reported by the free OpenOffice’s Calc spreadsheet and the open-source Gnumeric spreadsheet do not have the same numerical problems as does Excel - their programmers used accurate algorithms."""

It is not surprising because with an open source program everyone who can program can fix such issues, while with Microsoft you are at the mercy of the likely overworked Excel team.

teamonkey• 10 years ago

"Changes in Excel 2010 have improved its use for statistics considerably. For earlier versions of Excel, however, the answer is generally ‘No’. The following refers to versions prior to 2010 (2011 on the Mac)."

twunde6• 10 years ago

I disagree. Where I work, we have one person that does all the hedging, price calculations and other financial modeling and he only uses Excel. The only practical downsides to his use of Excel it's that he doesn't have direct database access so we generally need to create the initial reports to give him the data and secondly Excel can only hold a little over 2M rows.

blahi6• 10 years ago

It can hold many more if you use Power Pivot.

twunde6• 10 years ago

Good to know.

throw_away_7773• 10 years ago

How do you verify that his calculations are correct?

twunde6• 10 years ago

His calculations are usually compared to invoices (ie do the numbers match what we were charged for), other reports and historical data. Similarly when one of our DBAs or BAs creates an important report, we check the results against other data sources.

shostack2• 10 years ago

Why do you think this can't be verified? You can see the formulas in cells, or you can see the macro code. Further, you could auto import older data inputs and outputs via data connections in Excel and set up some automated checks to flag if something is off.

Is there a use case where you think this wouldn't be possible?

rabboRubble• 10 years ago

The data, information layouts, and APIs provided by information brokers are well tested with Excel and straight forward. Excel really shines when used in financial environment.

The problems with Excel appear when the incoming data is "dirty". Outside the finance industry there less financial incentive to ensure data is reliably interpreted in Excel.

fnord1234• 10 years ago

Where I work, we have one person that does all the hedging, price calculations and other financial modeling and he only uses Excel.

We used to buy in signals from a company who did their work in Excel. I wrote some scripts to export the data and recalculate it in Python. Almost every month I found errors in their reports and had to ask them to fix it.

So I recommend you fight HARD to get someone to reproduce his work in a language that is visible and reproducible.

nightski3• 10 years ago

Your conclusion is that it was excel causing these errors and you are implying similar errors would not be made in Python. I think it is more because of your experience as a developer why you were able to spot and correct errors.

dandermotj5• 10 years ago

So just to give you an opposing perspective of what you just said: "we have __one__ person that does all...". So apart from the fact he can't access a database appropriately, he's the _only_ person who does this work and none of it is documented or reviewable because that's the nature of Excel. There's some serious operational risk in that. This guy disappears tomorrow and what do you do?

twunde6• 10 years ago

It's a misconception that work in Excel can't be documented or reviewed. We have the seed data he uses and the resulting Excel files (with the formulas he used still in them). This is the equivalent to version control for programming. The results are also reviewed against 1-2 appropriate sources (invoices, historical data, multiple other reports built by IT) for accuracy. To your very legitimate point that he's the only one doing this work, if he left, long-term we would probably need 3-4 people to take over his work. Short-term, the company would probably need to put together a triage team of 5-6 people to take over his existing processes. We actually moved some of the hedging to a different team last year and we had to hire two business analysts and implement a industry-specific system to do so.

lordnacho4• 10 years ago

Why does he not simply read a tutorial on how to pull in data from a database?

twunde6• 10 years ago

I think it comes down to the fact that he hasn't needed to and that he prefers the tools he already knows. He's also not part of the IT department, so it would be similar to having your CFO have direct database access. Possible but probably not the best use of time.

nerdponx• 10 years ago

I'm pretty sure there is an ODBC plug-in for Excel

ygra• 10 years ago

I understood it as that the person didn't have database access, not Excel.

bereasonable3• 10 years ago

Excel can handle millions of rows if he brings the data into PowerPivot.

Note: I'm a PM on Excel

newman314• 10 years ago

Random point but Excel sure could use a CONTAINS function for substring matches.

I was looking for something like this yesterday and the solutions I found were quite ugly.

Ended up having to use DSUM/DCOUNT instead which is still inelegant when one has to to multiple lookups with slightly varying parameters.

ee8aq3g5c62• 10 years ago

Let's not forget the time Excel's bad UI contributed to a bombshell paper's faulty evidence in favor of fiscal austerity, probably prolonging the European recession: http://www.bloomberg.com/news/articles/2013-04-18/faq-reinha...

nolok• 10 years ago

Yeah no, if people who were decided by this paper took it at face value without checking the numbers made sense, it's not an excel ui's fault. As for the others, they would have picked that policy choice with or without that paper.

ee8aq3g5c62• 10 years ago

Sure, we could argue over multiple possible sources of blame and maybe we will conclude that Excel is not the most important. It seems nearly impossible to have that debate in a rigorous way. In any case, "the evidence will be ignored anyway" is not a good argument to use a tool that will produce bad evidence.

nimish• 10 years ago

Excel can calculate whatever you need but the main issue is that it will modify your data to be helpful like converting number strings to floats unless you tell it not to and even then...

lordnacho4• 10 years ago

Never, ever use Excel for anything other than prototyping, spot-checking data, or as a makeshift GUI while you develop a proper one.

I've seen several financial shops where people were moving millions of dollars around using Excel. IMHO, it's always an indicator of deficient processes and lack of coding skill. Yes, I do know that some clever people use it. They are productive in spite of excel, not because of it.

Your main problem with excel is not that some statistical function is missing, or wrong, or misleading. Sure, that's an issue, but you can live with a few things being wrong if you can detect them and fix them yourself. I'll come back to stats later...

The problem with Excel is it's damn near undebuggable. There's simply nothing in the way of someone making a beast of a calculation, with the flow going all over several sheets. You can even make things circular if you want. The data and the code are all together, mixed up. Was a number in a given cell written there by the VB code, or was it an input? You can use the auditing functions, but chances are you will see a spaghetti of arrows.

It's also non trivial to find differences between different versions. Typically the dude who is using excel has also never heard of Git or SVN, so you will see a load of sheets like "portfolio1" and "portfolio2_new_old" and so on. I don't know if it's changed, but when I was using Excel, the files had code files within them, rather than separate files, like we do with most other languages.

Of course you aren't forced to write crappy spreadsheets, but there's simply a tendency for people who don't code to be a bit messy. But Excel is positively inviting trouble. It's so flexible that anyone under a little pressure will hack in some extra bell or whistle, building up tech debt for future generations. It basically lulls the novice into thinking they can build anything.

Amazingly I've met several people in finance who pride themselves on spreadsheets that stretch over thousands of lines. I remember a billion dollar merger where the analyst in charge of the "modelling" showed me how they reached the line limit (I think that's gone now, so good luck!). It's as if making things complicated justified their salaries, so maybe that's why excel is so popular.

Now, about statistics. If you're doing anything non-trivial, you absolutely do not have space to look at all the individual numbers in your matrices. Just like if you're solving equations on a piece of paper, you need your own symbolism. You need to be able to give things names and see short statements at the appropriate level of abstraction. You probably need to be able to verify the pieces independently, too. So unit tests for various operations, that aren't intruding into the business logic.

ZanyProgrammer• 10 years ago

I could've sworn I've seen people who work in the financial and maybe data science industries who've praised Excel to the heavens on HN. Like on the same level as Python, R, etc.

I don't work in finance (or data science really) so I can't comment, but it seems merely a tool to me.

lordnacho4• 10 years ago

No way, not data science. How are you going to load in millions, possibly billions of individual numbers?

With finance there's a lot of things that seem like good fits, but only if you keep them small. Something like a personal budget is fine, where everything fits on a screen.

throw_away_7773• 10 years ago

There are lots of people who are data analysts who use Excel. Data scientists would more generally use more powerful tools, but Excel is still useful for some things. For example if you have to manually enter in a small amount of data and then perform some simple calculations on this data, Excel is a fine tool. If your data is stored in a machine readable format or you want to do complex analysis with it you generally shouldn't be using Excel.

Nicholas_C2• 10 years ago

I'm probably one of those people. You can't build a model in R or Python and walk a sales or finance VP through it. The other day I walked some auditors through a regression model built in excel. I could not imagine doing that in R or Python, their eyes would glaze over and think it's a black box. That's the beauty of excel, very easy for everyone to understand as the numbers are right in front of you.

kgwgk4• 10 years ago

Yes, I do know that some clever people use it. They are productive in spite of excel, not because of it.

Would they be more productive if you took Excel from them?

I remember a billion dollar merger where the analyst in charge of the "modelling" showed me how they reached the line limit. It's as if making things complicated justified their salaries, so maybe that's why excel is so popular.

How should the analyst write his model in a simple way? As a Java (or Haskell, or whatever firs your ideal of simplicity) program? Or maybe he could simplify the model until he can write it down in a piece of paper.

jnbiche2• 10 years ago

How should the analyst write his model in a simple way? As a Java (or Haskell, or whatever firs your ideal of simplicity) program?

In Python or R, like the rest of us do. Or SAS, Stata, or even SPSS if they want a familiar interface (although using the SPSS GUI instead of its scripting interface will put them at risk of the same kinds of mistakes as Excel is).

Ensuring reproducibility and robustness isn't rocket science, and it doesn't require the obscurity of Haskell or the verbosity of Java to do properly. But it does require learning to script to program their models, instead of relying on GUI (which should be perfectly natural to analysts and their long Excel formulas).

Edit: To be clear, when I say "scripting" here, I mean text-driven programming, as opposed to using a GUI interface (which is rarely robust or reproducible, and certainly isn't in plain Excel).

lordnacho4• 10 years ago

Yes, if people didn't think that crappy tools were OK, they would have better tools, and they'd have a better idea of what they could do. For instance, I did a lot of fixed income spreadsheets at one point. Instructive for learning how the business works, but ultimately if you stay with the spreadsheets, you are not full exploiting opportunities. If I were to come back to that I'd automate the opportunity finding as well, going back through time to validate. That's something you just can't do with an excel sheet.

Models that take up entire spreadsheets are not models. There is never enough data to validate the sheer number of degrees of freedom that such models contain. There would be things like sub-models of entire divisions of firms. Who even has data that could validate all the potential things that could happen? These spreadsheets are pure ludic fallacy; advisors pretending they know how adding or removing some employees will affect some merger.

0x145555• 10 years ago

Excel is the de facto standard for my STAT 301 class.

ramblenode5• 10 years ago

Related discussion: https://news.ycombinator.com/item?id=12370605

galkk• 10 years ago

"statistics don't lie quote but liars use statistics"

Well, the similar can be said about authors of that article (don't know about newer version of Excel though).

Basically they are saying "Of course, it all appears only to old versions of Excel but there were sooo much problems with them".

So, what?

godzillabrennus• 10 years ago

Harper Reed gave a fireside chat at 1871 in Chicago a year back where he said that he's yet to see any meaningful data opportunity that can't be addressed by Excel. Most are just trying to build a solution where there isn't a problem.

ramblenode5• 10 years ago

He has apparently never worked with a dataset of more than 1,048,576 observations, Excel's row limit [0].

[0] https://support.office.com/en-us/article/Excel-specification...

princeb2• 10 years ago

there are a few unique issues i face with excel beyond the stats package.

1. if you shift cells (ctrl c, ctrl v) around, delete or insert rows, you may mess up existing cell references in formulas without realizing it. your vlookups, hlookups will not change your column numbers just because you did. your vba code will not change your A1 cell references. things will blow up here in spectacular fashion.

2. if you have a massive spreadsheet with a lot of lookups, UDFs, non-static cell values (like a Bloomberg real time feed), it's not so clear which UDFs in which cells get calculated in what order. sometimes it results in #value errors, which is a million times more desirable than if there was a iferr(..., 0) or iferr(..., "") and you can't tell if there was a failure.

3. your macros will happily destroy your work if you let it by mistake (writing over formulas in the wrong sheet etc). python will generally not destroy the code it's running.

4. AUTOFORMAT will destroy, without any honor or humanity, any data if it just barely looks like it should be something else. I've had strings get converted into dates, 0s get stripped off (I think geneticists also face that same issue), all kinds of nonsense.

some of the problems I see raised here in HN (such as errors in formulas, sanity checking) are also issues in other tools like scipy, matlab. common errors in these languages are off-by-one matrix references, terminating loops prematurely (esp for numerical solutions), formulas not written correctly, brackets in the wrong place or + instead of -, typos in variable names, nan versus 0 vs na, these are things that affect excel equally.

otherwise, excel is pretty good. it's quick to prototype, it gives passable charts if all you need are passable charts. it's very good at displaying intermediate results. it's pretty ok for WSYWIG presentation, formatting, especially if you have custom reports to produce every week rather than regular ones that tex can solve. the biggest thing about excel is that everyone uses excel and if you try to send over results in a non-excel format they'll (clients or whoever) ask you to send it back in xls.

oh also... mediocre workers can produce excel sheets of passable quality. mediocre works may not even produce a single scipy script of any quality. I have seen some horrific matlab code, written by people with engineering background. i've seen one guy, in his desire to make a programming language look just like excel, write a single line for each of the 50 charts he creates and calls them Chart1, chart2, chart3, rather than use a for loop even though it's just 2 lines. it's totally bizarre.

avs7333• 10 years ago

don't use v/hlookup...use index(match()) and you will solve that problem

Also, the folks running R should pay Matlab/Mathworks to give them a workshop on writing help files.

viraptor2• 10 years ago

your vlookups, hlookups will not change your column numbers

If you're not using tables yet, you should. They solve that problem easily, and make addressing more explicit (relative to the table name rather than the sheet)

princeb2• 10 years ago

you can still use table names in vlookups, and it will still not solve your problem because vlookups do not work by table headers but by column numbers

ramblenode5• 10 years ago

A point I have not seen mentioned is that Excel encourages bad practices for data visualization. No other statistics or data analysis software I am aware of gives you the option of 3D-ifying a 2D plot. This adds negative value to the plot for anyone who is actually interested in data over eye candy. Simple example is a pie chart. Give it depth and it becomes much more difficult to reason about, and one's reasoning could easily change if the chart was rotated.

learningman2• 10 years ago

Agreed for the most part, although there are rare valid uses for 3d graphs. I think most contemporary data vis people would say that Excel makes it possible to make pie charts at all means it may encourage bad practices.

infinite8s• 10 years ago

What are those rare valid uses for 3D graphs?

ramblenode5• 10 years ago

I was hesitant about the pie chart example but it was the simplest I could think of. I'm glad someone brought this up. ;)

Fej• 10 years ago

When an article title poses a question like that... the answer is probably no.

avs7333• 10 years ago

A couple notes from a heavy Excel and heavy R (and heavy several other similar products) user...

factories run on excel...terrifyingly so. I know semiconductor semiconductor fabs that basically run on queries in a many sheeted workbooks. The reason is because not everyone codes, especially not factory foreman, technicians, etc. They don't need a continuously running app (well they do but they don't know it) they just need formatted data.

That links to comment two. The issue that Excel solves over code goes back (IMHO) to two issues that come up with coders/noncoders. First, most users of excel are interested in the answer rather than the process. I get the value of being able to audit your steps visually, but the transition from Excel to R is a transition from the primary visual being data (i.e., results) to process. Depending on your perspective that can be highly meaningful.

Second, and probably more importantly, Excel lets you visualize your data as it progresses. For those with lower (or just different/less abstract) spatial and visual reasoning ability, seeing the progress of data from column to column can have a fairly profound effect. This extends to students who are trying to learn by seeing the progression as steps of a processing algorithm are applied. Doing so in code abstracts that process heavily. For some, especially those not used to treating data in the abstract/blind via code.

SmokyBorbon• 10 years ago

factories run on excel...terrifyingly so

Chemical plants too. But that does not mean there is no code.

An Excel document can be programmed with VBA to make connections to network resources, read/write files, send emails, etc.

I might not be the most effective tool for every task but it's everywhere and allows workers to automate work without making a request to hire a developer or buy new software. Someone can gradually automate business tasks on their own initiative rather than go through multiple layers of corporate bureaucracy.

avs7333• 10 years ago

absolutely agreed. I mean terrifying more in the sense of the lack of controls that I have typically seen in place and the way 'pull request equivalents' are handled and modifications are made.

hexane360• 10 years ago

I've spent a lot of time using Excel in ways it was never meant for.

If anyone is trapped using Excel to make a box plot, it is possible (disclamer please for the love of god never do this): Make a stacked column chart. The first series should be equal to Q1 minus the graph minimum. Format this to be invisible. Series two should be median - Q1, formatted as a black outline on a white bar. Series three should be Q3 - median, formatted like series two. Error bars can be added to give the whiskers. Here's an example: https://i.ytimg.com/vi/ucWmfmXb1kk/maxresdefault.jpg

Again, don't do this.

For a histogram, just make a min and max for each bin, and use countifs(range, ">=" & min, range, "<" & max) to get the amount in each bin.

bereasonable3• 10 years ago

Excel 2016 supports box and whisker plots.

itisbiz• 10 years ago

I've been recommending to all the people I work with to use Excel add-in Power Query to connect to data and do all calculations and transformations. Then they can just replace/update data sources and refresh queries. It's somewhat more accessible for non technical people than Power Pivot/ DAX/MDX. it's easy to make tabular dimensional record sets ready for consumption by pivot tables, Tableau, etc. You can choose to keep data in model instead of showing in worksheet to bypass row limits and connect model to pivot table. Best of all it teaches people ETL, better data management, separation of data and report.

fithisux• 10 years ago

Never, there are better alternatives and especially FOSS. For windows, PSPP is there to make your life easier. If you are a spreadsheet guy Libreoffice will make you never turn back to excel and can be programmed in StarBasic. R is amazing, but if you do not want to invest Scilab and Octave are there. if you are the freeware proprietary guy Google Sheets, WPS Office or FreeOffice are better alternatives than Excel. And if you have a good PC, why not give the FOSS giac/xcas a try, you may find your sanity.

Is Microsoft Excel an Adequate Statistics Package?

Related Stories