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:

    RANDARRAY(100000)

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

    NORM.INV(RANDARRAY(100000),$H$4,$H$5)

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

    VLOOKUP(NORM.INV(RANDARRAY(100000),$H$4,$H$5),$N$23:$O$179,2,TRUE)

  • And finally sum the 100K results

    =SUM(VLOOKUP(NORM.INV(_xlfn.RANDARRAY(100000),$H$4,$H$5),$N$23:$O$179,2,TRUE))

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
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.