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