MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Streamlining the Bennu Model With RandArray

October 03, 2018 - by Bill Jelen

Streamlining the Bennu Model With RandArray

Last week at Ignite, the Excel team introduced dynamic arrays. Today, a closer look at the RANDARRAY function.

Recently, in my entry to the Excel Hash game, I created a model to calculate the chance that the Earth will have a new tourist attraction, the Bennu Crater by 2196. That model performed thirty million calculations and required 200,001 formulas along with a 100-row data table. Here are the formulas used in 200,001 cells:

This model calculated in 10-12 seconds
This model calculated in 10-12 seconds

To simplify the model, you would use RANDARRAY(100000) instead of the RAND function. This will cause the formula to calculate 100,000 times.

  • You start by replacing RAND() with RANDARRAY(100000) to generate 100,000 answers:


  • Send the RANDARRAY in to NORM.INV to calculate 100,000 locations


  • Send the NORM.INV into VLOOKUP to determine if Bennu impacts the Earth:


  • And finally sum the 100K results


The final model to run 100,000 trials is contained in a single formula:

200,000 cells replaced by 1 formula
200,000 cells replaced by 1 formula

File size shrinks dramatically: From 3,270,979 bytes to 37,723 bytes. Recalc time is cut in half. Watch the Recalc times in the video below.

Watch Video

Download Excel File

To download the excel file: streamlining-the-bennu-model-with-randarray.xlsm

From now until the end of 2018, I am making my new Excel Dynamic Arrays Straight To The Point e-book free.

Excel Thought Of the Day

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

"Always start your table name with 'tbl'"

Title Photo: Roseanna Smith on Unsplash

Bill Jelen is the author / co-author of
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.