I haven't used Excel before except for basic functionality, but in the degree course I am studying we are using Excel to calculate probability. I have been given all the functions in the assignment and a photocopy of how the spreadsheet should look. However the random numbers need to extend over 500 rows and when i copy and paste row1 to row500 it will not generate the 500 results only the first.
If you want to do this without VBA install MOREFUNC click here and use the MRAND function - assuming that what you want is a range of non-repeating random numbers. However, please note that deliberately avoiding duplicates in a set of random numbers means that they are no longer statistically random.
to give more detail on this: the rows are laid out as follows (as set in the paper) A21= simulation no. (ie.1,2,3,4, etc....) B21= VLOOKUP(RAND()A10:C14,3) C21= E8+(E9-E8)*RAND() (E8 AND E9 BEING THE UNIFORM DISTRO) D21= NOMINV(RAND(),E13,E14) E21= (C3-B21-C21)*C4-C5
the instructions then say to to "copy A21:E21 to rows A520:E520 in order to provide the 500 sims"
i have tried copy and paste but it doesn't seem to work and I fear I am missing a trick.
When I ried it the problem i was facing repeated itself in that it changes the cell values of say the d21 formula to one up (ie. NOMINV(RAND(),E14,15) and so on. this then gives no value and a number error.