Excel in Statistics and Operations Research

I love spreadsheets

The spreadsheet package is a wondrous thing. It has revolutionized a great many processes in the office, home and scientific research. It has affected the way we think and teach. It has enabled many more people to program and to build models, without even knowing it (and sometimes very badly). And, for better or worse, Excel has become the default spreadsheet package.

I love spreadsheets and I love Excel.  I first became acquainted with Multiplan and Lotus-123 in 1984 as part of my graduate degree. It was amazing to see how versatile these spreadsheets were. Since then I have also used and taught Visicalc, a couple of shareware spreadsheets and finally Excel. At home I have used Excel to plan holidays, make sense of time zones, organize my decorative egg collection, convert recipes, budget, run the children’s organization at my church, make crosswords and other learning activities, plan a layout for my little sun-room, keep track of my time when juggling home, work and study commitments, cater for functions and schedule work. It is a standing joke at work that if there is a problem I will use a spreadsheet to solve it. And if that fails try duct tape.

Videos about spreadsheets

I have even made YouTube videos about Excel spreadsheets. Our first one, Absolute and Relative References, was made for the class to help with teaching a concept we seemed to have to explain over and over. It came popular worldwide and was the springboard for other spreadsheets about Excel. The video on Linear Programming using Excel Solver uses Lego pieces to illustrate a product mix LP. At an INFORMS conference in San Diego the FrontLine representative voiced his approval of the video, and it has received consistent viewing internationally for many years. It is due for renewal, thanks to upgrades in Solver, but is still helpful for teaching the fundamentals of LP.

Excel and Statistics

There are different schools of thought about using Excel in the teaching of statistics and operations research. My view is that it is preferable to teach Excel in conjunction with another discipline than in a specific computer skills course. Excel plays a big part as a tool in the entry-level Quantitative Methods and Management Science courses taught here at the University of Canterbury.
Statistics is not mathematics, but when it is taught by mathematicians, they are often captured by the mathematical aspects of statistics. Because they understand formulas by looking at them, they think their students will do also. This is fine for a course in mathematical statistics. My experience is that most students of statistics are not in the class from choice, and many find the mathematics daunting. It becomes a barrier to understanding statistics. Using a spreadsheet can help to remove that barrier.
I am aware that the Statistics ToolPak in Excel is far from ideal. It doesn’t include a test for a mean, and the data handling capabilities are poor at best. It can’t deal with missing values, the dialog boxes are unintuitive, and it isn’t even easy to add in when you want to use it. There are no box plots and the histograms are a joke at best and usually incorrect. I would much rather use a dedicated statistics package such as Minitab or SPSS. But there are two over-riding reasons why I continue to have the students learn statistics using Excel. The first reason is that they may never see Minitab or SPSS again in their lives, but Excel or something like it will be in any business they are in. The other reason is that they are learning Excel skills as well – what my colleague charmingly calls CV (resume) expansion. Time and again I have received feedback from students on how the Excel skills have come in handy. Because they use Excel all this time in the course, for all calculations, it becomes a tool, rather than a hurdle.

Graphing in Excel

Excel Graphs are ubiquitous. I wonder if the minion who decided on the default colors of maroon and beige in Excel 2003 smiled when he/she saw the myriad graphs produced in that color scheme. Certainly it was a flag to show whence they came and that the producer of the graph did not know enough to change from the default. Sadly some of the Excel graphs are awful. Tufte would not approve of all the trimming and empty space. Exploding three-dimensional pie-charts should be permanently exploded and abandoned. I realize it is not Excel’s fault that people like really bad graphs. I just wish it wasn’t so good at enabling them. A pie chart is another indicator that the analyst doesn’t really know what they are doing.

Despite all that it is fantastic that graphs are easy to produce in Excel. Graphing should be the first step in any data analysis, but manual production of graphs is tedious in the extreme. Excel makes it possible to have a good look at your data, quite quickly, and get an idea for what is happening, in terms of relationships and errors before blundering on with unnecessary analysis on dirty data. There is now no excuse not to graph. And Pivot Charts! These are SO cool. Once I got over the tricky interface and worked out how it all worked, I became a convert. My students are required to produce two-way bar charts and tables using Pivot-Tables and Pivot-Charts. I’m sure they will thank me for it one day. Some of them even do at the time!

Operations Research and Excel

In teaching Operations Research Excel is a boon. We can now escape from trivial Linear programs limited to two decision variables so that they can be plotted on the Cartesian plane. In our Management Science course we use Excel for teaching the idea of a model, for linear programming, multicriteria decision making, discrete event simulation and for financial models using discounting and annuities. There is a transformative opportunity to explore the models and see instantly the effect of a change in input value. Excel is a great introduction to Operations Research, and in an entry-level or MBA paper such as business methods, is sufficient. However any second level paper in OR should be using more appropriate software.
Teachers of decision sciences such as Statistics and Operations Research have the opportunity to teach good spread-sheeting practice, along with their own discipline. We need to be careful that we model best practice with regard to formatting, use of input cells, and avoiding numbers in formulas. This last video is a little silly, because I gave my editor a free-hand. However, the four style rules are still worth promulgating. Enjoy!