SEQUENCE and RANDARRAY Functions in Excel
September 28, 2018 - by Bill Jelen
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:
- Monday covered the new =A2:A20 formula, the SPILL error, and the new SINGLE function required in place of Implicit Intersection
- Tuesday covered SORT and SORTBY
- Wednesday covered FILTER
- Thursday covered UNIQUE
- Today will cover SEQUENCE and RANDARRAY functions
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:
The RANDARRAY function will be great for driving Monte-Carlo analysis. Specify how many rows and columns of RAND() you want:
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.
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.
FAILED ATTEMPT 1: For the Period argument, I tried putting in SEQUENCE(5), but, darn it, the fomula spilled and gave me five results:
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)).
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.
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.
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
