# 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

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 ofMicrosoft 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.