Random Number based on Normal Distribution

An Introduction to Excel's Normal Distribution Functions
http://www.exceluser.com/explore/statsnormal.htm

Excel 2003

The formulas are:Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

Note that the array formula in Cells E5:E13 is a special case.
First delete any array formula starting in Cell E5.
Then select Range E5:E13, paste the formula, and CONTROL SHIFT ENTER to get the { }.

In Cell A5 to D5, the formulas are copied down to row 13.

In Cell F5, the formula is copied down so that 400 rows are filled.

For your example with 100,000 participants instead of 400 you will need to either use Excel 2007 with more rows than Excel 2003, or make other arrangements.

The sheet will recalculate with any Cell change because volatile FUNCTIONS are used.

Directions for plotting two charts:

Create two named ranges Insert Name Define:
Norm1 =INDIRECT("B$5:B$"&$D$2+4)Norm2 =INDIRECT("E$5:E$"&$F$2+4)
Norm2 =INDIRECT("E$5:E$"&$D$2+4)
Also set the Range for the Category X Labels if it is not a set of integers starting with 1. In that case set the Range for the column that starts in Cell D5.

Two charts can be created with plot ranges of:
=NormDistSimulate.xls!Norm1
=NormDistSimulate.xls!Norm2
Where 'NormDistSimulate' is the name of the Excel file.

The first chart plots the PDF and the second plots the randomly generated data.

Here is another version where the INDIRECT Function has been eliminated and replaced with INDEX.

Also the Cell Array Formula in Column E starting in Cell E5 has been replaced with a formula that does not use the FREQUENCY Function.


Cell Formulas:

Cell Formulas in Row 5:
Excel 2003
The formulas in Cell A5 to E5 are copied and pasted for as many rows as are needed based on the Start and End in Cell D1 and D2.
The formula in Cell F5 is copied and pasted for as many rows as are needed based the Select number in D3.

NAMED FORMULAS FOR RANGES FOR PLOTS:
Norm1 =$B$5:INDEX($B:$B,$D$2+4)
Norm2 =$E$5:INDEX($E:$E,$D$2+4)

TESTING For:
STDin = 1
AVGin = 5
Select = 1000
where 0.9999970 of the bell curve is modeled
The calculation was done 1000 times with these resulting averages of the 1000 STDout and AVGout:
STDout = 0.981297785
AVGout = 4.99860900

NOTE:
In the previous post the cell formulas in Cell F1 and F2 should have had "D3" not "D2".

ILLUSTRATIONS BELOW:

This below shows the PDF charted in the upper plot, and the Resulting Count in Column E charted in the lower plot, with 40 Selected.



This below shows that the plot of the Resulting Count in Column E more closely resembles the PDF as the number Selected goes up (500 here).



Sometimes a #N/A Error results in Column E as below after the calculation repeats hundreds of times, possibly from the RAND Function.
If this occurs it may be necessary to exit Excel and restart the program.

CORRECTION<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
The Cell Formula in Cell E5 in the previous post was not correct.

Incorrect:
<o></o>
<o>

Cell Formulas
Range	Formula
E5	=COUNTIF(F5:INDEX(F:F,D$3+4),D5)

</o><o>Corrected:</o>
<o></o>
<o><o>

Cell Formulas
Range	Formula
E5	=COUNTIF(F$5:INDEX(F:F,D$3+4),D5)

This caused the numbers in column E to be undercounted.

The Start, End, and Select numbers should be integers.
Start should be at least 1 for the formulas posted to work.
If Start = 0 then the End number will not occur.</o></o>

Hi Jack,

I've tried following your solution and I've the following question:
- I'm trying to get 20 random no.s. These random no.s will be able to comply with a predefined mean and standard deviation values.

- Am I correct to say that the no.s from cells F5:F24 are the random no.s I could use?

- I could not match STDout with STDin and these 2 values are always quite different from each other. Any solution for this?

Sorry for the noob question.