Abstract

To generate a standard normal distribution you can use =NORMSINV(RAND()). If you need a normal distribution with a mean of 7 and a standard deviation of 10 then use =NORMINV(RAND(),7,10). But: Your normal distribution will not show a mean of exactly 7 (and a standard deviation of exactly 10) unless the number of draws approaches infinity. If you want to ensure a mean of 7 and a standard deviation of 10 then shift an originally generated series of random numbers to this mean and zoom it to the required standard deviation:

sbGenNormDist_01_Screen

Appendix sbGenNormDist Code

Please read my Disclaimer.

Option Explicit

Function sbGenNormDist(lCount As Long, _
    dMean As Double, _
    dStDev As Double) As Variant
'Generates lCount normally distributed random values
'with mean dMean and standard deviation dStDev.
'Source (EN): http://www.sulprobil.com/sbgennormdist_en/
'Source (DE): http://www.bplumhoff.de/sbgennormdist_de/
'(C) (P) by Bernd Plumhoff 24-Jul-2011 PB V0.2
Dim i As Long
Dim dSampleMean As Double, dSampleStDev As Double

If lCount < 2 Then
    sbGenNormDist = CVErr(xlErrValue)
    Exit Function
End If
ReDim vR(1 To lCount) As Variant
With Application
For i = 1 To lCount
    vR(i) = .NormInv(Rnd(), dMean, dStDev)
Next i
dSampleMean = .Average(vR)
dSampleStDev = .StDev(vR)
For i = 1 To lCount
    vR(i) = dMean + (vR(i) - dSampleMean) * dStDev / dSampleStDev
Next i
sbGenNormDist = .Transpose(vR)
End With
End Function