Forum Discussion
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 result so that the file works with all versions of Excel?
Cheers,
Joe
4 Replies
Jmuffins without the sheet or at least the formula/context in which it is used, that is real hard question. RANDARRAY() has a number of parameters including the row and columns, start and end, and integer or not. The most universal solution I can think of would be to create a table matching the rows and columns needed all with =([end]-[start])*RAND()+[start] where [start] and [end] are the start and end used in the RANDARRAY. If it should only return integer you will need to enclose it with INT().
Then highlight the table and define that region as a NAME like RANDA (you do this by typing the name in the name box to the left of the formula entry box or using Name Manager in Formulas -> Name Manager). Then replace RANDARRAY(...) with RANDA but and this is a BIG but, if it is used multiple times in many formulas and such you need to repeat the above each time to not only match the rows, cols, start, end but also because if you point to the same name and hence same table then each case will use the SAME set of random numbers which means those previously independent random numbers sets will no longer be different and random.
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.
