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 Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.