One of the 7 new dynamic array functions is RANDARRAY. In today's episode, I re-visit the model that I built for Excel Hash. That model used 200K cells with VLOOKUP, RAND, and NORM.INV. In today's video, replacing the formulas with a single formula. Recalc times are cut in half.
Transcript of the video:
Learn Excel from MrExcel podcast episode 2245 Streamlining the Bannu Model with Arrays Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Well, last week at Ignite, John Campbell, one of the Excel project managers had a session and here's a quote from John.
He said, "we heard from an MVP who replaced 200,000 cells with a single dynamic array formula. Recalc time was cut in half, file size cut by 99%.
That is a crazy claim.
But the model, that he's talking about was my entry in the Excel Hash game, Will Asteroid Bannu Impact the Earth?
Alright, in my original model, I ran 100,000 trials of asteroid Bannu based on data from NASA Jet Propulsion Laboratory. There were a 100,000 cells of NORM.INV using Rand and then a 100,000 cells of VLOOKUP, looking up those into the JPL table. And then 1 SUM to track how many impacts there were over a 100,000 trials. Then, a What If data table ran this whole thing a 100 times.
That file was 3,270,979 bytes.
I was able to replace those 200,000 in one cell with a single cell that has SUM(VLOOKUP(NORM.INV(RANDARRAY(100,000). That generated a 100,000 Rand arrays.
Everything else the same, What-If data table runs the same thing in a 100 times, file size 37,723, which is 1.2% of the original size.
Now, let's take a look at Recalc times.
All right.
Here's the stripped-down version of the model that I used for Excel Hash. These formulas, the NORM.INV of Rand. There's a 100,000 of those. And then a VLOOKUP comma true; comma true, that's the fast version, into a tiny table of about 160 rows.
All comes up to one SUM function of all those VLOOKUPs to figure out the probability of Bannu hitting the earth in 2182 to 2196. And then over here, a data table that runs that whole model, I think a 100 times. All right, so we'll calculate how long this takes using the original model.
And it's 10.8 seconds. 10.8. Click, okay. We'll run it again.
10.75. And run again. 10.73.
Now, how can we make this faster?
Using the Rand array function. So the first thing we can do is, we can take that whole NORM.INV and put it inside the VLOOKUP and I could have done this before dynamic arrays. That would have cut the 200,000 cells down to 100,000 cells without a doubt.
But then, instead of having a 100,000 cells with Rand, I can ask for Rand array and ask for it to return a 100,000 results.
It's going to lift the NORM.INV and the VLOOKUP and cause a 100,000 of these and a 100,000 of these to happen.
And I want this all just to come back into a single wrapper function, to let me know how many we get.
Okay. Now this workbook is still active down to AH100,000.
So, let's close and save. All right. We're in a new version of the file - the array version of the file. This time when I press End Home, it's only active down to row 180 and that's because of the LOOKUP table back there.
And we will calculate in this version.
Now again, this one formula is doing a 100,000 Rand array, sending it into NORM.INV sending that into VLOOKUP.
So, 100, 200, 300, thousand calculations plus 1. 300,001 will calculate.
5.3 seconds. Calculate.
5.43.
Calculate.
5.39.
It's faster. It's definitely faster!
Now, one interesting thing here that I can't quite explain is, one of the reasons that it's faster is because of this data table.
If I had taken the data table out, then the Recalc times between the original 200,000 cell model in this one cell model are about the same.
It's actually a little bit slower in this version and that's strange, because anytime you're using the table function Excel is forced into single threaded calculation.
So why being forced back into single threaded calculation does this?
Run in half the time of 200,000 cells. I can't quite explain that.
Well, these new dynamic arrays are amazing. They'll be coming to Office365 Make sure not to get Excel 2019. It won't have them.
You have to wait till Excel 2022. Check out my new book, Excel Dynamic Arrays: Straight to the Point.
Click that "i" on the top right hand corner.
This book will be free through the end of 2018.
Wrap up for today.
All right. The forecast model. With a 100,000 RAND, NORM.INV, V LOOKUP and 1 SUM, replaced with one single formula using Rand array.
The average time to calculate, 10.76 seconds for almost 200,000 cells. 5.37 seconds.
49% of the Recalc time. To download the workbook from today's video, visit the URL in YouTube description.
Wanna thank you for stopping by. Will see you next time for another netcast from MrExcel.
Well, last week at Ignite, John Campbell, one of the Excel project managers had a session and here's a quote from John.
He said, "we heard from an MVP who replaced 200,000 cells with a single dynamic array formula. Recalc time was cut in half, file size cut by 99%.
That is a crazy claim.
But the model, that he's talking about was my entry in the Excel Hash game, Will Asteroid Bannu Impact the Earth?
Alright, in my original model, I ran 100,000 trials of asteroid Bannu based on data from NASA Jet Propulsion Laboratory. There were a 100,000 cells of NORM.INV using Rand and then a 100,000 cells of VLOOKUP, looking up those into the JPL table. And then 1 SUM to track how many impacts there were over a 100,000 trials. Then, a What If data table ran this whole thing a 100 times.
That file was 3,270,979 bytes.
I was able to replace those 200,000 in one cell with a single cell that has SUM(VLOOKUP(NORM.INV(RANDARRAY(100,000). That generated a 100,000 Rand arrays.
Everything else the same, What-If data table runs the same thing in a 100 times, file size 37,723, which is 1.2% of the original size.
Now, let's take a look at Recalc times.
All right.
Here's the stripped-down version of the model that I used for Excel Hash. These formulas, the NORM.INV of Rand. There's a 100,000 of those. And then a VLOOKUP comma true; comma true, that's the fast version, into a tiny table of about 160 rows.
All comes up to one SUM function of all those VLOOKUPs to figure out the probability of Bannu hitting the earth in 2182 to 2196. And then over here, a data table that runs that whole model, I think a 100 times. All right, so we'll calculate how long this takes using the original model.
And it's 10.8 seconds. 10.8. Click, okay. We'll run it again.
10.75. And run again. 10.73.
Now, how can we make this faster?
Using the Rand array function. So the first thing we can do is, we can take that whole NORM.INV and put it inside the VLOOKUP and I could have done this before dynamic arrays. That would have cut the 200,000 cells down to 100,000 cells without a doubt.
But then, instead of having a 100,000 cells with Rand, I can ask for Rand array and ask for it to return a 100,000 results.
It's going to lift the NORM.INV and the VLOOKUP and cause a 100,000 of these and a 100,000 of these to happen.
And I want this all just to come back into a single wrapper function, to let me know how many we get.
Okay. Now this workbook is still active down to AH100,000.
So, let's close and save. All right. We're in a new version of the file - the array version of the file. This time when I press End Home, it's only active down to row 180 and that's because of the LOOKUP table back there.
And we will calculate in this version.
Now again, this one formula is doing a 100,000 Rand array, sending it into NORM.INV sending that into VLOOKUP.
So, 100, 200, 300, thousand calculations plus 1. 300,001 will calculate.
5.3 seconds. Calculate.
5.43.
Calculate.
5.39.
It's faster. It's definitely faster!
Now, one interesting thing here that I can't quite explain is, one of the reasons that it's faster is because of this data table.
If I had taken the data table out, then the Recalc times between the original 200,000 cell model in this one cell model are about the same.
It's actually a little bit slower in this version and that's strange, because anytime you're using the table function Excel is forced into single threaded calculation.
So why being forced back into single threaded calculation does this?
Run in half the time of 200,000 cells. I can't quite explain that.
Well, these new dynamic arrays are amazing. They'll be coming to Office365 Make sure not to get Excel 2019. It won't have them.
You have to wait till Excel 2022. Check out my new book, Excel Dynamic Arrays: Straight to the Point.
Click that "i" on the top right hand corner.
This book will be free through the end of 2018.
Wrap up for today.
All right. The forecast model. With a 100,000 RAND, NORM.INV, V LOOKUP and 1 SUM, replaced with one single formula using Rand array.
The average time to calculate, 10.76 seconds for almost 200,000 cells. 5.37 seconds.
49% of the Recalc time. To download the workbook from today's video, visit the URL in YouTube description.
Wanna thank you for stopping by. Will see you next time for another netcast from MrExcel.
