Excel for Engineers and other STEM Professionals Part 8: Array calculation using Defined Names

In the last article I introduced the Modern Dynamic Arrays that wereannounced at the end of September by Microsoft’s John Campbell andJoe McDaid [1]. As yet, I have not been in a position to try the new functionality hands on, so I had considered suspending this series of articles until such time as I am able to present array functionality taking into account both the Office 365 environment and that offered by the traditional single purchase versions.

That may yet come to pass but then I realised that I had not covered the use of defined names for developing array formulas in any depth. As I was browsing Chandoo.org I came across a problem posed by a contributor called Eloise. She was having a problem evaluating a definite integral. This set me to thinking about how I could use Excel to visualise and evaluate definite integrals and I realised that I could use this problem to demonstrate how an array function may be evaluated over an arbitrary length array without ever displaying the array on a worksheet. Instead the output will be a graph of the function and a definite integral will be calculated using a basic trapezoidal rule from values held in memory. In this article I will demonstrate how the techniques we have been covering in this series can be extended to develop the visualizer shown in Figure 1. Readers interested in using the visualiser or would like to see how the tool was constructed can download the Excel file atnafe.ms/excel8