Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Is Microsoft Excel an Adequate Statistics Package?

practicalstats.com

Post reply on HN
Print view View original

81–90 of 108 posts

Re: Is Microsoft Excel an Adequate Statistics Package?

#81

Post by SmokyBorbon »

avs733 wrote: post #69

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…

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

Re: Is Microsoft Excel an Adequate Statistics Package?

#82

Post by princeb »

viraptor wrote: post #80
princeb wrote: post #42

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…

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

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

Re: Is Microsoft Excel an Adequate Statistics Package?

#83

Post by Nicholas_C »

ZanyProgrammer wrote: post #28
lordnacho wrote: post #26

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. You…

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.

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.

Re: Is Microsoft Excel an Adequate Statistics Package?

#84

Post by itisbiz »

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.

Re: Is Microsoft Excel an Adequate Statistics Package?

#85

Post by Nicholas_C »

kprybol wrote: post #2

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…

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.

Re: Is Microsoft Excel an Adequate Statistics Package?

#86

Post by [deleted] »

kprybol wrote: post #2

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…

[deleted]

Re: Is Microsoft Excel an Adequate Statistics Package?

#87

Post by xapata »

twunde wrote: post #53
dandermotj wrote: post #27

Earlier quoted context omitted.

It is absolutely the interface to excel that causes errors. I can take your excel spreadsheet, format cells, add things, change a reference and generally fuck it up and hand it back to you and you would never know. Whereas, if I change a text file then you can see what's changed. Similar errors are much less common in a programming language.d

When you're working with people who are good with Excel, they will notice. If you're only ok with excel and just use vlookups, then you're probably going to have problems, especially since those people don't tend to keep a versioned history of their files. Really the main difference is that version control has become an ingrained habit in software development whereas with Excel it's rare

Ah, a True Scotsman is good at Excel. I understand now.

Re: Is Microsoft Excel an Adequate Statistics Package?

#88

Post by tangue »

Mikeb85 wrote: post #18
tangue wrote: post #3

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.

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.

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

Re: Is Microsoft Excel an Adequate Statistics Package?

#89

Post by viraptor »

princeb wrote: post #82
viraptor wrote: post #80

Earlier quoted context omitted.

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

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

You can use either index/match as avs733 suggested, or alternatively:

    =VLOOKUP(value, Sometable, MATCH("Column2", Sometable[#Headers]))

Re: Is Microsoft Excel an Adequate Statistics Package?

#90

Post by bereasonable »

hexane360 wrote: post #70

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 -…

Excel 2016 supports box and whisker plots.
Post reply on HN