Excel - 200K Cells Replaced With 1 RANDARRAY - Episode 2245

Microsoft Excel Tutorial: Walkthrough of replacing 200,000 formulas with one RANDARRAY formula.

Welcome to the MrExcel netcast, where we bring you the latest tips and tricks for mastering Excel. In this episode, we will be discussing the power of dynamic arrays and how they can streamline your Excel models. Specifically, we will be looking at how 200,000 cells were replaced with just one RANDARRAY formula, resulting in a 99% reduction in file size and a 50% decrease in recalculation time.

At the recent Ignite conference, Excel project manager John Campbell shared a quote from an MVP who was able to replace 200,000 cells with a single dynamic array formula. This may seem like a crazy claim, but it is true. The model in question was my entry in the Excel Hash game, "Will Asteroid Bannu Impact the Earth?" In its original form, the model ran 100,000 trials of asteroid Bannu using NORM.INV and VLOOKUP formulas, resulting in a file size of 3,270,979 bytes.

However, by using the new RANDARRAY function, I was able to condense those 200,000 cells into just one cell, resulting in a file size of only 37,723 bytes. This is a 99% reduction in file size! But the benefits don't stop there. The recalculation time was also cut in half, from 10.8 seconds to just 5.3 seconds. This is a significant improvement, especially for larger and more complex models.

But how does this all work? By using the RANDARRAY function, we can generate a dynamic array of 100,000 random numbers, which are then used in the NORM.INV and VLOOKUP formulas. This eliminates the need for 200,000 individual cells, resulting in a much more efficient and streamlined model. And the best part? This new feature is only available in Office365, so make sure to upgrade if you want to take advantage of it.

If you want to learn more about dynamic arrays and how to use them in your Excel models, be sure to check out my new book, "Excel Dynamic Arrays: Straight to the Point." It's available for free until the end of 2018, so don't miss out on this valuable resource. And as always, you can download the workbook from today's video by visiting the URL in the YouTube description.

Thank you for tuning in to this episode of the MrExcel netcast. I hope you found this information helpful and that you will join us again for more Excel tips and tricks. Don't forget to click the "i" in the top right corner to access more resources and information. See you next time!

#excel
#microsoft
#microsoftexcel
#excelhacks
#microsoft365
#walkthrough
#excelnew
#excelformula
#excelformulasandfunctions
#evergreen
#exceldynamicarray

This video answers these common search terms:
how to use randarray in excel
when to use randarray excel
how to do a simulation in excel
how does simulation work in excel
can excel do monte carlo simulation
how to add a monte carlo in excel
how to run a monte carlo simulation in excel
how to do a monte carlo in excel
how to do a monte carlo simulation in excel

Table of Contents:
(00:00) Streamlining the Bennu Asteroid model with Arrays
(00:15) Replacing 200,000 cells with a single dynamic array formula
(00:25) Reduction in file size and recalculation time
(00:39) Original Bennu model with 100,000 trials and 3,270,979 bytes
(01:15) Replacement of 200,000 cells with a single cell formula
(01:39) Comparison of file sizes and recalculation times
(02:00) Stripped-down version of the model used for Excel Hash
(02:23) Calculation time using the original model
(03:09) Using RANDARRAY function to make the model faster
(03:38) Calculation time for the array version of the model
(04:29) Comparison of recalculation times with and without data table
(05:02) Explanation for faster calculation time with single threaded calculation
(05:14) Promotion for Excel Dynamic Arrays: Straight to the Point book
(05:27) Clicking Like really helps the algorithm

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.

[wayneedmondson]

Hi Mr. Excel.. wow.. these new functions area really mind blowing.. like adding a whole new dimension to where EXCEL can take you. Thanks and Thumbs up!

[ahmedal-dossary]

It’s worth trying..!!

Thanks Bill and Microsoft for making our lives easy.

[excelisfun]

Wow!!!! That is profound. I teach simulation in my statistics classes using all the distribution functions and various randomized variables, and this significantly I can't wait to get these new Dynamic Array Functions! Thanks for the video, Mr Excel. P, .S. I tries to find the 10 Thumbs Up button, but I could not find it!?!?!

[simfinso]

if we put all Data in a table using ctrl "t" & giving a name to table &then use this trick will it be more Faster?

[htie]

Crazy claim checked out, rather than believing it wholesale at first hearing 👂

[ماقلودل-ظر]

#Mrexcel
I want to make messege to allow me know quantity on hand inventory in excel please how ????

[MrAshfaqahmed]

I have question to ask,

I want to display first and day of week, week starts from Thursday to Wednesday, if the start date of month is on tuesday then first date of week will be tuesday of the current month, same case on last date of week, if week ends on monday then last date of week will be monday of only in current month, without going to next month last week day.

Out expected
For last month
1-09-2018 to 05-09-2018
27-09-2018 to 30-09-2018

For current month
1-10-2018 to 3-10-2018
4-10-2018 to 10-10-2018
11-10-2018 to 17-10-2018
18-10-2018 to 24-10-2018
25-10-2018 to 31-10-2018