An Important Reminder

can only be successfully written in Excel 365 or later, because it will lodge its results in multiple cells.

Back to the Basics

Press Enter, and the deed is done. One formula, multiple instances of FORMULATEXT (again, “spilling”), and no brackets. Just remember that you can’t write this one in earlier versions of Excel.

And that spilled range bears a closer look. We’ve written the formula in C1, and of course a click on that cell will disclose what we’ve written there. But click on C2 and the same formula will appear, but dimly – in a kind of shadow emanation of the original. You won’t be able to delete C2, or any cell that’s been spilled by the source formula. Delete C1 – the cell featuring the actual formula, on the other hand – and the entire spill range will vanish.

Now here’s an even simpler example, but again, one you can’t emulate in past versions. If you key in a set of names down a column, for example, Figure 2-2,

A table with 4 rows. The values read John, Paul, George, and Ringo.

anywhere in the worksheet (or any other sheet in the workbook, by default), and immediately spill those names down its column.

That’s pure dynamic arraying at work, and that kind of range can offer to the thoughtful user more than gimmickry. A teacher could list all her student names in a range and call it Class, and by typing =Class anywhere else in the workbook could immediately summon all the names for grading, attendance, or any other administrative purpose. A work supervisor could do the same with a staff roster; type the range name, and the roster appears.

And by way of review of our discussion in Chapter 1, be reminded that writing =Beatles in pre-365 Excel and tapping Enter would result in “John” – and only John.

Now for the record, a multi-cell workaround of sorts did avail itself to the pre-365 generation. If I were to select four cells first, proceed to click in the formula bar and enter =Beatles, and then enter Ctrl-Shift-Enter, the names of each of the Fab Four would indeed make a place for themselves, one to a cell. But that’s a tricky – and inefficient – ask.

The One-Formula Grader

Now for something slightly different, let’s look at a hypothetical multiple-test answer key, and compare it to the responses managed by a student in the file Single Student Grades, for example, as illustrated by Figure 2-3.

A table of 3 columns and 10 rows. The column headers are quest dot, answer key, and student. The values are given for all 10 rows.

The answer key occupies B4:B13, alongside the student’s replies in C4:C13. The obvious objective: to determine the student’s overall grade.

What’s new here is the recourse to the IF function, its formula comparing the answer to question one to the student’s response: if the two match, the student receives one point; if they don’t, the formula issues a zero. Next of course, we could copy the formula down the D column and cap the process by jotting a SUM function somewhere in the worksheet (Figure 2-4).

A table of 4 columns and 11 rows. The column headers are quest dot, answer key, student, and blank. The values are given for all 10 rows. The blank column has the formula IF and SUM on the right side of the values in the final row.

(We could go further and divide the correct-answer total by the number of exam questions, returning a score of 60%; but that step isn’t necessary for our demo purposes.)

And that’s indeed a bit different from previous exercises. As with the character-count exercise featuring LEN, SUM wraps itself around an inner function, in this case IF; but here, and unlike LEN, the IF statement repeatedly appraises each pair of values making their way down their respective columns, for example, B4 with C4, B5 with C5, etc. In true array fashion, the 1’s and 0’s ordered up by the IF statement look like this inside the formula pictured in Figure 2-5.

A screenshot of the function arguments window. It has input boxes for the options, logical test, value if true, and value if false. An arrow points to the equal to value at the bottom.

We’ve just encountered another instance of lifting – this time a special case called pairwise lifting, in which each pair of values sprawled across their rows are compared, as the formula spills its results down the D column. Our single IF formula has been made to tear up its job description and evaluate data in a range of cells; and those 1’s and 0’s – all housed in the inner sanctum of the formula – total 6, the student score.

We’ll see in Figure 2-6 the following.

A table of 4 columns and 10 rows. The column headers are quest dot, answer key, student, and blank. The blank column has the formula IF on the right side of the first cell.

Shades of LEN(A1:A3). Here too, the IF statement – minus SUM – assigns its 1’s and 0’s alongside each student answer. Again: one formula that disgorges multiple, spilled results in their cells – an outcome only possible in Excel 365 and beyond.

The Return of the Brackets

And here’s one final, introductory need-to-know. Suppose we want to write a VLOOKUP formula that would enable us to enter the name of any student in cell I9 (file: Multiple Student Grades) and calculate his/her average for the sociology as well the art exam, the data furnished here in cells A1:G11 (the range has been named All), and captured by Figure 2-7.

A table of 7 columns and 10 rows. The column headers are name, s o c, Philosophy, Political Science, Art, Physics, and Chemistry. The values are given in each row.

Once again, we’re asking a formula to step out of character – the venerable VLOOKUP had been genetically tweaked to look up the values in a single column, and now we want it to track down the values in two columns at the same time – and compute their average, besides. What would that VLOOKUP look like?

And we learn that Jane averaged 69 for the soc and art exams.

But there are those brackets again – the ones you thought had been permanently banished from array formulas, and tucked irretrievably into the archives. But those brackets were of the automatic variety, the ones that leaped into a cell whenever you put the finishing touches on an old-school array formula via the now-obsolete Ctrl-Shift-Enter. These brackets are user-selected and user-typed, and they throw an actual, on-the-fly array smack-dab into a formula. The values between the brackets are called array constants, so named because they’re hard-coded, or simply typed. But don’t get the wrong idea – the constants aren’t inert text: here they serve to quantify the column numbers you select, and so in our case they tell VLOOKUP to search for data in columns 2 and 5. If we had entered {2,5,7}, the VLOOKUP would go ahead and compute Jane’s average for soc, art, and chem: 64.67. The bottom line is this: the array constants force VLOOKUP to undertake a multi-column search for values. And once again, because all this derring-do yields a one-celled answer – the test average – our formula can be written in previous versions of Excel, but remember that if that’s where you find yourself, you’ll need to ratify the formula with Ctrl-Shift-Enter.

This time Jane’s scores for soc, art, and chem will appear in actual, adjoining cells (Figure 2-8).

A table of 7 columns and 10 rows with values. The column headers are name, s o c, Philosophy, Political Science, Art, Physics, and Chemistry. The formula V LOOKUP is given on the bottom right for the entry Jane and its values.

And again, that output confirms the multi-cell capability of dynamic array formulas. You’re doubtless getting the idea.

Getting Re-oriented

But that last formula raises a new question. You’ll note the horizontal alignment of Jane’s three scores; but why do they proceed across a row, and not down a column instead?

where the array values are parted by semicolons, Jane’s scores would spill down a column, as in Figure 2-9.

A table of 7 columns and 10 rows with values. The column headers are name, s o c, Philosophy, Political Science, Art, Physics, and Chemistry. The formula V LOOKUP is given on the bottom right beside the value 66 of the entry Jane.