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:
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
Download
Please read my Disclaimer.
sbGenNormDist.xlsm [25 KB Excel file, open and use at your own risk]