This covers the Excel SEQUENCE and RANDARRAY modern array functions.
=SEQUENCE(Rows, Columns, StartAt, Step By)
=RANDARRAY(Rows,Columns)
Do these Excel function because people can't figure out how to have the Fill Handle make 1 drag to 1, 2, 3?
No! The SEQUENCE function makes every Excel function into an array function.
Using Sequence inside of IPMT function to replace an amortization table.
To use the workbook from today's video: https://wwww.mrexcel.com/download-center/2018/09/sequence-and-randarray-functions.xlsx
=SEQUENCE(Rows, Columns, StartAt, Step By)
=RANDARRAY(Rows,Columns)
Do these Excel function because people can't figure out how to have the Fill Handle make 1 drag to 1, 2, 3?
No! The SEQUENCE function makes every Excel function into an array function.
Using Sequence inside of IPMT function to replace an amortization table.
To use the workbook from today's video: https://wwww.mrexcel.com/download-center/2018/09/sequence-and-randarray-functions.xlsx
Transcript of the video:
Learn Excel from MrExcel podcast episode 2237.
SEQUENCE and RANDARRAY functions. Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen and 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! RANDARRAY, alright, so =RANDARRAY generates... let's generate 10 rows of random numbers. Of course, between 0 & 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 and of course if you watch this podcast you know hold down the control key or select the 1 in 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?
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 and his Excel team gave us this function. Why did he give us this function?
Because it's gonna solve all sorts of problems.
Let's say you're buying a house, $495,000 pay for over 360 months, four and a quarter percent 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 need to calculate the interest payment for each of those months.
And see that's the second argument right ther, is which period which means that I have to take that number one and copy it down like that and then refer to that certain numbers over there.
So in the first five months of that loan my interest, total interest, is gonna be eight thousand seven hundred forty-one dollars.
Look this whole thing becomes really really simple with the SEQUENCE function.
So right here, instead of saying period one, I'm gonna ask for the SEQUENCE of 1 to 5, like that.
BAM! Oh no, that is not what I wanted. I didn't want five answers.
I want one answer.
Wrap that whole thing into the SUM function Control shift enter. No, oh.
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.
Twelve rows, one column.
Starting in 1 or 13 or 25 or 37.
Wrap that whole thing into the SUM function and it works.
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 semiannual 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
SEQUENCE and RANDARRAY functions. Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen and 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! RANDARRAY, alright, so =RANDARRAY generates... let's generate 10 rows of random numbers. Of course, between 0 & 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 and of course if you watch this podcast you know hold down the control key or select the 1 in 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?
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 and his Excel team gave us this function. Why did he give us this function?
Because it's gonna solve all sorts of problems.
Let's say you're buying a house, $495,000 pay for over 360 months, four and a quarter percent 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 need to calculate the interest payment for each of those months.
And see that's the second argument right ther, is which period which means that I have to take that number one and copy it down like that and then refer to that certain numbers over there.
So in the first five months of that loan my interest, total interest, is gonna be eight thousand seven hundred forty-one dollars.
Look this whole thing becomes really really simple with the SEQUENCE function.
So right here, instead of saying period one, I'm gonna ask for the SEQUENCE of 1 to 5, like that.
BAM! Oh no, that is not what I wanted. I didn't want five answers.
I want one answer.
Wrap that whole thing into the SUM function Control shift enter. No, oh.
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.
Twelve rows, one column.
Starting in 1 or 13 or 25 or 37.
Wrap that whole thing into the SUM function and it works.
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 semiannual 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
