# excel formula help

harps1h 12:24 02 Mar 08
Locked

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.

Can anybody help in what I maybe doing wrong?

Fermat's Theorem 16:44 02 Mar 08

tends to confuse, and the habit should be avoided :-)

Hope this helps :-)

VoG II 16:59 02 Mar 08

What a cool piece of coding :o)

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.

harps1h 18:10 02 Mar 08

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.

harps

VoG II 18:15 02 Mar 08

Your formula in B21 has a syntax error that I presume is a typo.

Select A21:E21. Hover the cursor over the bottom right of E21 and the cursor should turn into a +. Hold down the left mouse button and drag down to row 520 then release the mouse button.

harps1h 18:17 02 Mar 08

yes it was a typo if you are referring to a missing comma. i will try your tip now.

harps

VoG II 18:18 02 Mar 08

Also I think that some of those formulas should have absolute addresses otherwise it doesn't make much sense. E.g B21

= VLOOKUP(RAND(),A\$10:C\$14,3)

harps1h 18:34 02 Mar 08

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.

harps1h 18:51 02 Mar 08

vog i looked at your last thread and realised my mistake in not inserting the \$ symbol and once this was replaced it has worked perfectly.