excel formula help

  harps1h 12:24 02 Mar 08

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 :-)

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

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)
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.


  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.


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

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

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

An overview: What leading creative agencies are doing to improve diversity

New iPad, iPhone SE & Red iPhone 7 on sale now