Box and whisker plots in Google Sheets

And then everything changed. For the first time since the loss of Gnumeric a spreadsheet was displaying a box and whisker plot. To back up to the beginning, MS 150 Statistics was built around spreadsheets. Students in the course were not statistics majors and the overwhelming majority were not in a scientific field. Most would wind up working on office desktops for which they would not be an administrator. Downloading R would not be an option and R would be steep learning curve for some for whom computers are an unfamiliar technology.

Spreadsheet based statistics, for all the faults and potential for error, would be at the center of the course. The use of Ubuntu in the classroom meant using LibreOffice.org or Gnumeric. Gnumeric provided the capability of including box and whisker plots. A change in computer laboratory technology to Microsoft Windows five years later saw the use of Gnumeric on Windows - until Gnumeric stopped supporting Windows. A further change of my work top to OSX forced the retirement of Gnumeric as a spreadsheet supported by the course. Besides, the Schoology learning management system that I had adopted could not convert Gnumeric files. Excel, for all the complexity of the ribbon interface, saw increasing use by students.

For box plots I turned to BoxPlotR, although the package had some timing out issues for the class.

Although I had played with Google Sheets for eight years, the lack of the "t" functions (TINV, TDIST, TTEST) limited the usefulness of the package. The recent introduction of the "t" functions, the full functionality of the LINEST array function, the ability to integrate tables and charts into Google Docs, and the integration with Schoology converged to make Google Docs a viable choice. I was missing only box and whisker plots, but then LibreOffice.org, OpenOffice.org, and Excel also have no box plot option.

I had overlooked add-ons, though I was vaguely aware such existed. With increased use of Google Docs in association with Schoology assignments, I began exploring add-ons.

And discovered that Google Statisticians and Engineers had produced a Statistics add-on.

That does box plots. Oddly enough, as of late 2017 and early 2018 the add-on no longer appears in a search of add-ons as seen above, but the add-on still exists.

Including outliers.

And histograms. And density plots.

Including, again, outliers. In the number of green MMs found in a case of MMs.

Even Normal Quantile Plots.

And nothing was the same ever again. There was clearly no reason to ever use LibreOffice nor Excel. There were only a few edge cases in which Excel or LibreOffice would still be useful - fitting a second order polynomial on an xy scattergraph for example. Google Docs and Sheets now has the capability to support all of my courses - statistics, physical science, and ethnobotany. Yes, there are still some gaps, but they can be filled. Such as the inability to add a trendline and trendline equation to an xy scattergraph. But the work around is to use the SLOPE and INTERCEPT spread sheet functions. I already have that work around on the "material to be added" list for my physical science text.

Now I am left with much to ponder. Some experimenting with the automatic histogram generator suggests that Google Sheets makes reasonable choices. My present process of having the  statistics students build class upper limits by hand and then use the frequency function is complex and confusing. The goal was a histogram, should the machinery be tossed in favor of using Google Sheets add-on? Should I simply teach the students to use the tool and how to interpret the results? Everything changed.