Forum Discussion

Copper Contributor

Feb 04, 2021

RANDARRY Equivalent for older version of Excel

Hello, I am having trouble sharing a model I've made in Excel to others as the RANDARRAY function only works in Excel 365. Does anyone know of a way to workaround this function, and get the same r...

excel

Formulas and Functions

office 365

Jmuffins

Copper Contributor

Feb 04, 2021

mtarler Thank you for your reply.

For context, it is a Monte Carlo simulation. In once cell the number of simulations is specified, and there is a RANDARRAY function which references the number of simulations as rows, with 1 column:

RANDARRAY($C$9,1)

This RANDARRAY command is within NORM.INV to randomly sample from a distribution, with mean and std.dev specified:

=NORM.INV(RANDARRAY($C$9,1),D4,D5)

As such, each random number will need to be different, but I want the formula to be able to copy down to as many cells as specified by the # of simulations cell.

mtarler

Silver Contributor

Feb 04, 2021

Jmuffins first off I am also assuming you need it to not use dynamic arrays. From what I'm seeing, that is the bigger issue than the function RANDARRAY().

Could you just fill a column with something like:

=IF(ROW()>$C$9+[some offset], "", NORM.INV(RAND(),$D$4,$D$5))

Jmuffins
Copper Contributor
Feb 04, 2021
mtarlerYou are correct, I understand that now.
The dynamic array was the issue.
The previous formula does work, thanks a ton!