MrExcel Publishing
Your One Stop for Excel Tips & Solutions

SEQUENCE and RANDARRAY Functions in Excel


September 28, 2018 - by Bill Jelen

SEQUENCE and RANDARRAY Functions in Excel

SEQUENCE and RANDARRAY Functions are the last of the seven new functions introduced this week at the Ignite Conference in Orlando. Here is a recap of the articles from this week:

SEQUENCE generates an array of numbers. The syntax is =SEQUENCE(Rows,Columns, Start, Step). While =SEQUENCE(10) generates 1 through 10, you can customize the numbers with =SEQUENCE(10,3,5,10) to generate a 10-row by 3-column array starting at 5 and stepping by 10:

Generate a sequence of numbers
Generate a sequence of numbers

The RANDARRAY function will be great for driving Monte-Carlo analysis. Specify how many rows and columns of RAND() you want:

30 random numbers
30 random numbers

What if you want random numbers between 11 & 19? Then you are back doing the same calculations before RANDBETWEEN came along: =ROUND(RANDARRAY(10,3)*9,0)+10.


An array of random numbers between 11 & 19
An array of random numbers between 11 & 19

Using SEQUENCE Inside Of Another Function

The screenshot below calculates the Interest payment for each of the first five months of a loan. You have to key in the numbers 1 through 5 in A7:A11 or use =ROW(1:1) and copy down. That trickery will go away soon.

Five formulas to calculate interest payments for five months
Five formulas to calculate interest payments for five months

FAILED ATTEMPT 1: For the Period argument, I tried putting in SEQUENCE(5), but, darn it, the fomula spilled and gave me five results:

I wanted one answer instead of five.
I wanted one answer instead of five.

Notice

The formatting in the image above is one buggy thing about these new Dynamic Arrays. If your formula is going to spill to five cells, you should format them first. In this case, Excel formatted the first cell but the formatting does not get copied. When I asked Excel Project Manager Joe McDaid about this, he replied: "It is a known issue. We wanted to get you all of the functionality now and fix the formatting later." That is a fair point. I want the functionality now and can worry about formatting later.

To return a single answer when Excel wants to spill to five cells, use a wrapper function, such as SUM: =SUM((IPMT($B$3/12,SEQUENCE(5),$B$2,$B$1)).

Five answers summarized to one cell
Five answers summarized to one cell

If you've ever created a loan amortization table just to figure out how much interest you will pay in year 3, the formula shown below does it in one formula. You still use the IPMT function. But for the third argument, you can specify SEQUENCE(12,1,37,1) to generate periods 37 to 48.

In the image below, the starting month is in column F. The formula figure out the 12 months starting from the starting month.

Easier than the full amortization table
Easier than the full amortization table

All this week, you've read about: SINGLE, SORT and SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY. But the new array functions are not limited to those 7 functions. In a demonstration of the lamest array function ever, I present an array of ROMAN() functions, generated using the SEQUENCE(12,8) function inside of ROMAN. Virtually every Excel formula is now an array formula, with no need for Ctrl + Shift + Enter.

Leave it to MrExcel to find the world's lamest use of the new modern array formulas.
Leave it to MrExcel to find the world's lamest use of the new Dynamic Array formulas.

Watch Video

Video Transcript

Learn Excel for MrExcel, Episode 2237: SEQUENCE And RANDARRAY Functions.

Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen. Well, this week we are going to talk about the new modern array functions -- two new ones today -- SEQUENCE and RANDARRAY. Check this out. =SEQUENCE. How many rows do I want? 10. Bam. There's the numbers 1 through 10. How many columns do I want? Give me 3 columns of that. Bam. Where do I want to start? Let's start at the number 3 and increment by 9. Bam. [=SEQUENCE(10,3,3,9)]

RANDARRAY. alright, so, =RANDARRAY, let's generate 10 rows of random numbers, [goes ā€“ 00:48] between 0 and 1. Now I need 10 rows and 3 columns of random numbers. Yes, all right. Now wait, give it all the good stuff we've seen this week: sort, sort by, filter, unique. Did they really just give us the SEQUENCE function because people can't figure out how to make the 1 go to 1, 2, 3, 4, 5? [=RANDARRAY(10,3)]

And, of course, if you watch this podcast, you know, hold down the CONTROL key or select the 1 and the blank cell next to it, right, or, heck, = this +1, grab the fill handle, and drag. There's plenty of ways to do that. Is that the reason they gave us the SEQUENCE function? [=S4+1]

No. That's not the reason at all. I mean it's an example, right? I can stop the video right here and we'd be done but that's not why [Joe McDade ā€“ 01:35] and the Excel team gave us this function. Why did he give us this function? Because it's going to solve all sorts of problems.

Let's say you're buying a house: $495,000, pay for it over 360 months, 4.25% interest rate. This is your payment right there. But, hey, I want to create an amortization table with the months going down the left-hand side and I need to calculate the interest payment for each of those months, and, see, that's the second argument right there is which period which means that I have to take that number 1 and copy it down like that and then refer to that set of numbers over there. So, in the first 5 months of that loan, my interest, total interest, is going be $8,741. [=PMT(B3/12,B2,B1)], [=IPMT($B$3/12,A7,$B$2,$B$1)], [=SUM(B7:B11)]

Look, this whole thing becomes really, really simple with the SEQUENCE function. So, right here, instead of saying PERIOD 1, Iā€™m going to ask for the SEQUENCE of 1 to 5, essentially. 1 to 5, like that. Bam! Oh, no, that is not what I wanted. I didn't want 5 answers. I want 1 answer. Wrap that whole thing into the SUM function. CONTROL+SHIFT+ENTER. No. Look at that. Same answer I got over there; one formula. No need to create the sequence of numbers. It just happens, which means that if all I want to do is calculate how much interest I'm paying in each year of the loan -- so I want to go from months 1 through 12, 13 through 24, 25 through 36 -- I just edit that formula and ask for the sequence, 12 rows, 1 column, starting in 1 or 13 or 25 or 37, wrap that whole thing into the SUM function and it works. [=IPMT($B$3/12,SEQUENCE(5),$B$2,$B$1)], [=SUM(IPMT($B$3/12,SEQUENCE(5),$B$2,$B$1))], [=SUM(IPMT($B$3/12,SEQUENCE(12,1,F10,1),$B$2,$B$1))]

These new modern arrays from the Excel team are awesome. Now, you need Office 365. You need that version which, if you're on the semi-annual plan, will probably reach you in January of 2019. For more information, check out my book Microsoft Excel 2019: Inside Out. Click that I in the top right-hand corner.

Well, hey, I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.

Download Excel File

To download the excel file: sequence-and-randarray-functions.xlsx

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Omit needless formats"

Title Photo: Farsai C. on Unsplash


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.