Excel statistics functions – simple answers to simple questions
I have colleagues who hate Excel with a passion. Why, they demand to know, would ANYONE use Excel for statistics when there are so many options that are so much better? Actually, I don’t find the Excel add-on for statistics that terrible, but that isn’t even the topic of this post.
I use Excel because sometimes:
- The data sent by the client is in Excel
- I can use Excel to answer the question in less time than it takes me to open another application.
Here is an example from today, the client needed to know for a few categories the average weekly income. They also were concerned that the employees doing data entry might have, in some cases, inadvertently entered weekly or monthly income instead of hourly. Relevant fact – the organization did not have any CEOs making thousands of dollars per hour.
First, find the averages:
=AVERAGE(J2:J139)*40
This takes the average of all cells in that column from the second to the 139th and then multiplies that average hourly wage by 40. Click on the corner of that cell and drag across to get the average for each of the columns.
Second, find the standard deviation
=STDEV(J2:J139)
Click on the corner of that cell and drag across to get the standard deviation for each of the columns.
Now, if your standard deviation is something like $2 or $4 per hour, you’re fine. If it is $43 per hour, then someone entered the weekly salary for that column. For the one column where that was the case, I sorted it and, of course, was immediately able to spot the person with the incorrect value.
The whole process took me about a minute to give them the means for the different categories and say, “Oh, by the way, record 47 was incorrect, I fixed it.”
Obviously, no one sends me a data set just to get the means for a few columns and this was just one of 60 different questions they needed answered. The objection to Excel I have heard is that is all some people know and so they use it for everything – “When your only tool is a hammer, every problem looks like a nail.”
That may be, but sometimes, you really DO just need to pull out a nail.
And that realization many years ago is how I overcame my prejudice against Excel.
the problem is when people do a basic OLS in Excel, and suddenly fancy themselves a statistician. I’ve seen this happen in corporate finance and risk management types a fair amount.
I think you are probably right that some people’s aversion to Excel comes from mis-use or overuse but like Jon Peltier said on twitter, I don’t think that explains the almost religious hatred you see from some corners. It’s just a tool.