The Power of Arrays: The Excel Tool That Performs Multiple Functions in a Single Step
Paul M. Goldwater, Timothy J. Fogarty · Journal of accountancy online/Journal of accountancy · 2007
One of the most powerful features of Excel is the array--a formula designed to act simultaneously on sets of two or more values in order to calculate other values. Yet, because arrays appear to be forbidding, few CPAs use them. This article is designed to dispel arrays' bad reputation and demonstrate how they can speed and simplify your work while making it less prone to errors. So get ready to overcome your bias against arrays. We'll begin with the most basic array formula, and as we move--step by step--to more complicated ones, you'll see how powerful arrays can get. To make it easier for you to follow along, download an Excel file from www.aicpa.org/download/pubs/jofa/mar2007/goldwater.xls. The file contains two versions of each worksheet. One worksheet in each set has blank cells in which you can practice entering the arrays and other formulas mentioned in this article, while the other has all the cells already completed. AVENGE IT Accountants often need to tightly summarize data. Exhibit 1 uses a one-dimensional array formula on payroll information to calculate the average pay of each employee and the global average of all employees. (In your downloaded file, see the Average It worksheet). [ILLUSTRATION OMITTED] Here's how we did it: To calculate the average per employee, select the range G3:G7 and type this formula: =(B3:B7+C3:C7+D3:Dg+E3:E7+F3:F7)/5 Then press Ctrl+Shift+Enter, which does two things: It automatically places curly brackets--{}--around the formula, labeling it an array formula, and simultaneously triggers the array calculation. The array formula now exists in G3 to G7 and cannot be changed except by rewriting the entire formula. To calculate the average pay per month, select the range B8:G8 and type in this formula: =(BB:Ga+B4:G4+B5:G5+B6:G6+B7:G7)/5 Then press Ctrl+Shift+Enter. The global average for all employees is now in cell G8. RANK IT Creating two-dimensional arrays is slightly more challenging. Consider again the payroll data of Exhibit 1. This time we want to rank the paychecks by size. To do that, copy the list of names (as shown in the lower half of Exhibit 2) and type this formula: =RAN K(B3:FT, B3:FT) [ILLUSTRATION OMITTED] Press Ctrl+Shift+Enter, and presto, the data are ranked--a task that would be far more difficult without arrays. ANALYZE IT Arrays also are useful when performing analyses that impose conditions upon mathematical operations. For example, say you have a spreadsheet loaded with sales data and you want to see various subsets of the data based on ranges of products' prices and quantity (see Exhibit 3). For this calculation we will use a single-cell array. (See the worksheet Single Cell in your downloaded file.) [ILLUSTRATION OMITTED] If we want to know the sum of range B3:B7 by range C3:C7, typically we could create D3:D7 and place the sum in cell D8. However, an array formula in D9 would do the job in one pass:{=SUM(B3:B7*C3:C7)} That simple formula extracts all the information from the underlying cells without the usual sum formulas in D3:D8. The grayed area at the bottom of Exhibit 3 (D9:D13) contains various array formulas to compute numerous values of interest to CPAs. Table 1 lists many of the typical ways CPAs are called upon to manipulate such data and the array formulas that perform each of those calculations. Advisory: Often CPAs need to know, and perhaps to explain to clients or executives who are not handy in Excel, how certain spreadsheet numbers are derived (see screenshot at right). You can display this information easily by clicking on Tools, Formula Auditing, Evaluate Formula. This allows you to step through the calculations, first seeing cell references and then the numbers those references represent. CALCULATE THE CONSTANTS Arrays often need to import constants, including useful explanatory information such as dates, names and numbers. …