Generating Lots of Random Numbers

[tpkcfa]

I need some help generating a list of random numbers from a normal distribution. I would like to write a program that will generate 1200 random numbers given user-defined mean and standard deviation parameters and then put them into a single column of cells. I have been able to accomplish the task but not in a very efficient manner. Basically, I've generated a number, put it in a cell, move down one cell generate a new number, move down one cell, etc. This is extremely time-consuming as each step causes every previous random number to regenerate. Is there a way to generate all 1200 numbers and then put them into the column all at one time?

Thank you for any advice or suggestions.

[mikerickson]

Please Login or Register  to view this content.

[Dav]

well the easiest way is to have the addin for analysis toolpak installed and then there is an add in that does all that for you.

Or am I missing something?

Regards

Dav

[tpkcfa]

I have a workbook with seven worksheets of formulas that will draw on the random number sequence for data, plus a cover page where a variety of statistics are summarized. Effectively, I want someone to be able to review the summary stats for one sequence, and then hit one button and have an entire new sequence generated where they can view the summary stats for that sequence. And so on. I don't want someone to have to go through the Analysis Tools Random Number Generation process each and every time.

[Dav]

what is the code you have got so far?

Regards

Dav

[Dav]

back to the previous suggestion, you can run analysis tools from a macro, so for example if you had the mean in cell d1 and the sd in cell d2

the following macro (which you could add as a button) would continually repopulate the range a1:a1200 every time it was run

Please Login or Register  to view this content.

Does that help?

[shg]

You could use the formula

=NORMINV(RAND(), mean, dev)

wrapped in some VBA to recompute the 1200 values on demand, assuming you're using Excel 2003 or beyond.

See http://www.exceluser.com/explore/statsnormal.htm

[tpkcfa]

Thank you all for your help. Dav, your code was exactly what I needed. I had been trying to do it that way, but kept failing for some reason. Thanks again.