Abstract
To generate a standard normal distribution you can use =NORM.S.INV(RAND()). If you need a normal distribution with a mean of 7 and a standard deviation of 10 then use =NORM.INV(RAND(),7,10).
But: Your normal distribution will never 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. You can achieve this with at least three different approaches:
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 30-May-2024 V0.3
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) = .Norm_Inv(Rnd(), dMean, dStDev)
Next i
dSampleMean = .Average(vR)
dSampleStDev = .StDev_S(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 [49 KB Excel file, open and use at your own risk]