# Streamlining the Bennu Model With RandArray

October 03, 2018 - by Bill Jelen 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:

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:

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

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