In an introductory course on elementary numerical solutions of partial differential and other complicated equations, there are many advantages to using MS Excel as the calculation platform as it has the advantage of presenting students with a familiar user-interface and eliminates the need for students having to learn a new software package or to write explicit procedures setting up point grids and visualizing the results in high-level language, so students can concentrate upon the actual algorithms. However, calculation errors creep in at random intervals when using Excel for solving partial differential equations using time and space iteration over a large number of time iterations (many thousands) and with a large number of spatial points (e.g. 1024).

An example is when the one-dimensional wave equation (where ψ is the displacement variable and c is the wave velocity) is solved using a simple iterative method based upon difference approximations for the first spatial and time derivatives and of the respective second derivatives¹ (analogous to the relaxation method of solving Laplace’s equation): (where time points indexed by t are separated in time by $∆t$ and spatial position points indexed by x are separated in distance by $∆x$). Figure 1 shows the ideal solution to Equation (1) when ψ is constrained by nodes that are a well-defined length apart and at t = 0 is subjected to a point impulse at 1/8 of its length from one node. This is a simple model of a stretched string struck by a hammer as in a piano.

Figure 2 shows a typical 1024 space-point simulation run on a Lenovo 2.9GHz, 8 cores, 32GB RAM computer using Windows 11 Pro and Excel Office 16. An error occurs at a random time; other computers (e.g. Dell 3GHz, 6 cores, 8GB RAM, Windows 10 Pro, Excel Office Plus 2016; and Acer 3.2GHz, 2 cores, 4GB RAM, Windows 10 Pro, Excel Office 19) also produce comparable errors after a different random time, so this is clearly not a shortcoming of one particular computer or manufacturer. Once a single calculation error occurs, of course the wave equation solver propagates the error to right and left at the wave velocity.

These errors are repeatable in the sense that they almost always occur well within the number of iterations needed for a complete solution of one cycle of this repetitive problem, but they are random in the sense that the errors occur at unpredictable times on each run of the program. This means that they are clearly not caused by a deterministic programming error on the part of the user; rather, a pattern-sensitive hardware issue or an operating system (paging) bug is the cause of this behavior.

The precise cause can be investigated by running the application with three iterations running simultaneously so that an error in one simulation can be verified and identified as an error by comparison with the other two simultaneous simulations. The usual cause on a variety of computers tried out was found to be unreliable copying of a result at the current time point t into the storage for the previous time result (t – 1) (or the equivalent when copying into the result for (t – 2)), needed for numerical evaluation of the second time derivative. By programming a halt in the iteration procedure as soon as the comparison between all three iterations fails, the erroneous results can be examined as shown in Figure 3.

Therefore, the solution is to repeat this storage step so that if it were not performed the first time then it will be the second time; if not performed correctly the second time, it will have been performed the first time anyway. Thus, the iteration procedure is now, in sequence, where Equation (4) repeats the assignment of Equation (3) in case Equation (3) was not completed correctly, Equation (5) updates ${\psi }_n(t-2)$ with the value for the previous time point, Equation (6) repeats this in case Equation (5) was not completed correctly, Equation (7) updates ${\psi }_n(t-1)$ with the value for the previous time point, and Equation (8) repeats this in case Equation (7) was not completed correctly.

Without this assignment repetition, Excel fails generally within one or two thousand iteration time steps on many typical office computers (see Figure 2(b)), so on including the repetition the probability that the first and second attempts both fail is vanishingly small for several thousand iteration steps. As a result, Excel can be used as a platform for teaching the fundamentals of solving partial differential and other complicated equations without the problem of computational errors appearing at random. The penalty is, of course, slower operation than without the repetition, but erroneous behavior without the assignment repetition can also be used as a teaching example of what can happen without close scrutiny of numerical results and how to find a work-around to mitigate the issue. A typical result is shown in Figure 4 where the computation is stable for many more iterations than shown in Figure 2(b).

While Excel is not reliable enough for solving complicated partial differential equations without taking action to mitigate errors, by judiciously repeating the variable assignments a reliable platform can be obtained.

R.C. Woods is Associate Dean of Engineering with responsibility for research and graduate affairs, and Professor of Electrical & Computer Engineering at the University of South Alabama. Previously he was a Lecturer and subsequently Senior Lecturer (widely regarded as equivalent to Assistant and Associate Professor respectively in the U.S.A.) in Electronic and Electrical Engineering at the University of Sheffield (UK), Professor of Electrical and Computer Engineering at Iowa State University, and Japan Program Director in the Office of International Science and Engineering at the National Science Foundation (Virginia, U.S.A.). For his work at NSF he received the NSF Director’s Award for Collaborative Integration recognizing his “exceptional teamwork in representing a model for a truly collaborative activity across the Foundation involving science, administration, and science diplomacy.” He studied at New College in the University of Oxford where he earned Bachelor’s and Master’s degrees, and the Doctor of Philosophy degree for his original research on magnetic resonance and related phenomena in rare earth metal alloys conducted at the University of Oxford’s world-famous Clarendon Laboratory. He was also awarded the rare D.Sc. Higher Doctorate by the University of Oxford acknowledging his distinguished research achievements over many years, and was elected a Fellow of the Institution of Engineering and Technology. He is a Program Evaluator for the Engineering Accreditation Commission of the Accreditation Board for Engineering and Technology.