I have been teaching an undergraduate engineering course this term, and have beenn very frustrated that the first tool students reach for to solve any quantitative problem is a spreadsheet (generally Excel, but my comments here I think are generic to spreadsheets of all flavors).
I remember when I first used a spreadsheet (Lotus123 anyone!). It was an eyeopener that one could replace pencil and paper and a calculator to do simple arithmetic manipulations. After all, it was motivated by business/accounting/financial users where addition, subtraction and perhaps various interest rate calculations were all that were required.
Spreadsheets evolved to where calculations of various levels of sophistication can be performed, and with the ability of some (e.g. using macros) to programming, perhaps highly complex engineering calculations can be done in these environments. However, just because they can be does not mean that they should be.
Maslow’s Law of the Instrument is quoted as “I suppose it is tempting, if the only tool you have is a hammer, to treat everything as if it were a nail” (https://en.wikipedia.org/wiki/Law_of_the_instrument). Its use is truly frequent amongst students. Having learned that they can use spreadsheets for calculations (even if they have also learned how to program) often means that they regard that as their preferred, and even only, tool to do calculations.
I would argue that given the prevalence of numerically sophisticated programming environments, whether your preference is Matlab, Python, Mathematica, or some other platform with high level numerical and graphical capability (for a lot of data analysis, I personally use R), their use is to be preferred. Here are some reasons:
- Without a lot of effort in constructing the spreadsheet, commenting, naming variables, etc., it is very difficult to debug or ascertain where a calculation may have gone off the rails. This becomes very difficult when teaching. A presentation by IBM in the context of statistical analysis notes that 88% of spreadsheets have at least one error.
- A corollary to the above is the difficulty in communicating the procedure used to achieve a result either to others, or to some future user — without extensive documentation.
- Autocorrect errors in spreadsheets have been documented for data, for example in the field of genetics.
- Algorithms are not often fully disclosed or references. For example, below is the help screen for the Bessel Y function from Office 365. In contrast, details of the algorithms used for this function are disclosed in Python, R, to a lesser degree in Mathematica, Unfortunately details for this function’s implementation in Matlab are similarly unclear.
In contrast, use of a programming language encourages separation of data (e.g. as an input file) from calculations used to process the data. Algorithms have (generally) been more documented. The use of actual descriptive variable names (e.g. “volume” rather that “B2”) makes the logical flow of analysis clearer. Programming languages also facilitate (and good practice encourages) the use of commenting to walk anyone reviewing the code thru the logical flow.
Engineers still need to pay attention to units. In spreadsheets this is an issue. Within programming environments, there are some packages and add-ons that allow consideration and conversion of units — although some of these need further development.
To some degree, I would advocate a “back to the future” approach. In the days of pencil and paper, laying out the data and a stepwise delineation of computational steps (and assumptions) was an intrinsic skill needed for education of engineering students, and practice.
The availability of spreadsheets, IMHO, has encouraged sloppy thinking in engineering calculations, and has hindered instruction in good practice.
Who is ready to teach an engineering class where spreadsheets for presentation and documentation of calculations are disallowed? I am seriously thinking that I am.