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