excel formula help

  harps1h 12:24 02 Mar 2008
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 2008

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

In the meantime does VoG's Excel Tips and Tricks click here solve your problem?

Hope this helps :-)

  VoG II 16:59 02 Mar 2008

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 2008

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 2008

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 2008

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 2008

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 2008

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 2008

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.
thank you for your time

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

Is this the future of VR and AR?

Best iPad buying guide 2017

Comment regarder le Bureau des L├ęgendes en ligne ?