What’s New About Sequence

Of course, Excel has long afforded us the means for unrolling a sequence of values across, or down, a spreadsheet. If you needed to enter a skein of numbers 1 through 100, we’d just enter the 1 and the 2 – thus establishing the desired interval, or step, of 1 – grab onto the AutoFill button, and pull it down 98 more rows, or across for as many columns. Moreover, if you had already entered a list of 100 names, you could post the 1 and 2 in the adjoining left column, select that tandem of values, and double-click the AutoFill button. Presto – 3 through 100 would appear.

will elaborate a sequence 30 rows high, one column wide, begin the sequence with the value 1, and step, or increment, each succeeding value by 1. In other words, write the above formula and you wind up with numbers 1 through 30 diving down a column.

will sequence the 1–30 values across a column. But here you do need to enter that initial comma, in order to alert the formula that the 30 has leapfrogged the row argument and bounded into the column reference.

30 rows’ (and one column’s) worth of values starting with 6 and stepping up in increments of 2 will result, for example, in 6,8,10,12, etc.

And you’ll fashion, as we see in Figure 4-1.

A table has 3 columns and 10 rows. The function involved is, equals sequence (10 comma 3 comma comma 4). The number 1 in column 1, row 1 is highlighted.

Note, by the way, that here the sequence spills across its rows, and not down.

This happens in Figure 4-2.

A table has 2 columns and 12 rows. The function involved is, equals sequence (12 comma comma comma 0). The number 1 in column 1, row 1 is highlighted.

The 0 step means just that; nothing is added to the next sequenced value. Start with 1 and end up with 1 – 12 times.

And let’s remind ourselves that all of these sequences radiate from a single formula. Remember – we’re in dynamic array territory.

The Possibilities Proliferate

Now that you’re getting the hang of SEQUENCE you can begin to think of all sorts of ways in which the function can do your bidding, sometimes in conjunction with other functions, and with some judicious formatting.

Start with a simple but useful task. You’re managing an expanding list of names that you’ll like to number automatically, per Figure 4-3 (file: Numbered List).

A table has 2 columns and 4 rows. The column headers are number and name and the row entries in column 2 are Emily, Jack, Arthur, and Sally.

And that counts off the sequence of values 1 through 4. Enter additional names and the count is correspondingly raised, feeding more numbers to the sequence in Figure 4-4.

A table has 6 columns and 7 rows. The column headers are number and name and the function involved is, equals sequence (count a (B 2 colon B 1000) ).

Simple but useful indeed, though a couple of cautions need be sounded. For one, don’t try to assign IDs to names via this technique, because if you sort the names, the numbers won’t follow along with them. The SEQUENCE formula merely counts the names, but has no interest in which name currently finds itself alongside any spilled value. And secondly, remember – you can’t write the above formula to a table.

Spates of Dates

will elicit a series of dates poured down five rows, pulling across seven columns, anchored by the current date (whenever that is), and incremented by one day, for example, Figure 4-5.

A table has 7 columns and 6 rows. The function involved is, equals sequence (5, 7, today ( ), 1). The data 4 forward-slash 13 forward-slash 2022 in column 1, row 1 is highlighted.

And treat the spill to the appropriate date format.

44562 signifies January 1, 2022, and so our formula will sequence all the days of 2022 down one column – and enable you to enter notes associated with each date in the column to its immediate right. Remember that the omitted arguments here – the column count and the step number – default to 1.

To break down the formula: the 13 attests to the number of rows required by the sequence – 13, because the first row returns 6:00 a.m., and we need 12 more to mark out the intervals for all 12 hours.

The 6/24, which could have alternatively been written ¼ or .25, puts the start value of the sequence in place – because 6/24, or one-quarter of the day, translates to 6:00 a.m. And the fraction 1/24 issues the step, or increment, that bumps each value in the sequence upwards. 1/24, after all, is spreadsheet language for one hour, and so the sequence spills 6/24, 7/24, 8/24, and so on, until it comes to a halt at 18/24, or 6:00 p.m. Formatted in time terms, we get in Figure 4-6.

A table has 1 column and 13 rows. The function involved is, equals sequence (13, , 6 forward-slash 24, 1 forward-slash 24). The data 6 colon 00 colon 00 A M in column 1, row 1 is highlighted.

PMT, Again: The Sequel, with Sequence

Now that you’ve honed your SEQUENCE chops, let’s make our way back to the PMT exercise we stepped through last chapter, in which we let loose a dynamic array formula across a flurry of loan interest/payment-period possibilities in Figure 4-7.

A table has 6 columns and 10 rows. The entry in row 1 is 10000 dollars and the entries in row 4 are 2, 3, 4, 5, and 6 percent. Entries in column 1 start from the fifth cell from the top, entries are 12, 24, 36, 48, 60, and 72.

Five columns, a start number of .02 (2%, of course, in the desired formatting), and an interval of .01.

And those expressions prompt a rewrite of the actual PMT formula in G8 as illustrated by Figure 4-8.

• =PMT(G7#/12,F8#,G4)

A table has 8 columns and 12 rows. 2 functions are involved in rows 3, 5, and 12. The data, 2 percent in row 4, column 4, and 12 in column 3, negative 842 point 39 in column 4, row 5 is highlighted.

(And note the pound-sign references in the formula.)

Our results here reproduce those we achieved through conventional means in Chapter 3, and so they beg the obvious question: why bother? The answer is that now we can cycle through many more payment possibilities, by simply editing the formulas. For example, replace the 5 with an 8 in the interest rate SEQUENCE, as in Figure 4-9.

A table has 11 columns and 12 rows. 3 functions are involved in rows 3, 5, and 12. The data, 2 percent in column 4, row 4 and 12 in column 3, negative 842 point 39 in column 4, row 5 is highlighted.

which would enable you to enter any value in G1 without having to edit the formula. The G1 entry would record as many interest intervals as you require.

And of course, you can do much the same for the payment frequencies, by substituting a new value for the 6 in its SEQUENCE. Thus we see that by working dynamically, new scenarios can suggest themselves in a flash.

The One-Formula Multiplication Table

Now let’s move from time to times. Suppose we want to construct a multiplication table – not of the old-school kind (literally), but rather a dynamic table, one that can grab any two ranges of values and flash all their multiplied products immediately. SEQUENCE can do the job – and with a grand total of one formula.

Start with a blank worksheet and enter the numbers 5 in B6 and 10 in C6. Those entries will impart, on our first go-round, the values 1 through 5 down a column and 1 through 10 across a set of columns, helping to form the outline of a matrix that will multiply every value by every other one.

And as we see in Figure 4-10.

A table has 11 columns and 9 rows. A function equals sequence (B 6) asterisk sequence (, C 6) is involved in column 2, row 3. Data 1 in column 2, row 4 is highlighted.

Cool, if I do say so myself. Now enter different values in B6 and C6, and the table changes to reflect the new inputs, with the number of columns and rows expanding and contracting accordingly. It’s dynamic.

In fact, this formula takes us back to a lesson we’ve already learned, that is, our discussion in Chapter 3 of how perpendicular ranges behave toward one another. There we noted that if we multiply two such ranges, each value in the first range is multiplied by every value in the second – and that’s exactly what’s happening here. The first SEQUENCE formula dispatches its values down a column, and the second pushes its values across, at a right angle to the first. The difference here is that the two SEQUENCE formulas haven’t scooped up their values from ranges in the worksheet, as they did in Chapter 3; the 1-5 and 1-10 sequences have been assembled inside their formulas, after which all the results spill onto the matrix.

Rewriting the CODE

We’ll close our look at SEQUENCE with another follow-up to Chapter 3, an important addendum to the discussion there of the array we wrote inside MID, that in turn nested itself inside the CODE function, recalled in Figure 4-11.

A table has 9 columns and 6 rows. The row entries of row 3 from left to right are 84, 104, 117, 114, 115, 100, 97, and 121 and the function involved is located in column 1, row 5.

which of course sequences to 1,2,3,4,5,6,7,8, emulating the bracketed activity in Chapter 3; and those eight values again extract the word’s eight characters, after which the CODE function identifies the code of each. But SEQUENCE is far more efficient, because if we enter a different word in C6 of any length, SEQUENCE(LEN(C6)) will count its characters, too, and deliver the count to CODE, for example, Figure 4-12.

A table has 6 columns and 11 rows. The data 100 in column 1, row 2 is highlighted and the function involved is, equals code (Mid (C 6, Sequence (Len (C 6 ) ), , 1 ) ).

And that’s clearly the more effective and powerful approach.

That little comma preceding LEN pushes LEN(C6) into SEQUENCE’s second argument, where it moves into column territory.

Looking Ahead

But I do go on. You’ve doubtless gotten the point of SEQUENCE by now, and with just a few practice go-rounds you’ll be a SEQUENCE savant. Now we can pay a visit to another member of the dynamic array crew – UNIQUE.