CODEWITHSUNDEEP
excelrandommean
Green Hill
Green Hill

Reputation: 23

Excel how to find real random data set from mean and standard deviation?

In Excel it is easy to find the mean and standard deviation given 10 single data points. But is it possible to generate 10 random data points in Excel given the mean and standard devation?

In Excel I can right click "View Code" and access a macro to use macro coding, but how would I go about coding this?

Sub rand()
  Range("B4").Formula = "=NORMINV(RAND(),$B$14,$B$15)"
  Range("B4").Calculate
  Range("B4").Value = Range("B4").Value
  Range("B5").Formula = "=NORMINV(RAND(),$B$14,$B$15)"
  Range("B5").Calculate
  Range("B5").Value = Range("B5").Value
  Range("B6").Formula = "=NORMINV(RAND(),$B$14,$B$15)"
  Range("B6").Calculate
  Range("B6").Value = Range("B6").Value
  Range("B7").Formula = "=NORMINV(RAND(),$B$14,$B$15)"
  Range("B7").Calculate
  Range("B7").Value = Range("B7").Value
  Range("B8").Formula = "=NORMINV(RAND(),$B$14,$B$15)"
  Range("B8").Calculate
  Range("B8").Value = Range("B8").Value
  Range("B9").Formula = "=NORMINV(RAND(),$B$14,$B$15)"
  Range("B9").Calculate
  Range("B9").Value = Range("B9").Value
  Range("B10").Formula = "=NORMINV(RAND(),$B$14,$B$15)"
  Range("B10").Calculate
  Range("B10").Value = Range("B10").Value
  Range("B11").Formula = "=NORMINV(RAND(),$B$14,$B$15)"
  Range("B11").Calculate
  Range("B11").Value = Range("B11").Value
  Range("B12").Formula = "=NORMINV(RAND(),$B$14,$B$15)"
  Range("B12").Calculate
  Range("B12").Value = Range("B12").Value
  Range("B13").Formula = "=NORMINV(RAND(),$B$14,$B$15)"
  Range("B13").Calculate
  Range("B13").Value = Range("B13").Value
End Sub

This is my random data generated based on mean and std dev, then i will use this data to find mean and std dev, if the mean and std dev did not mean the value i want then redo until it correct, so what code i need to add to make it redo?

Upvotes: 0

Views: 1529

Answers (1)

Gary's Student
Gary's Student

Reputation: 96771

Without VBA:

Say we want a set of values with a mean of 7 and a std-dev of .005

In A1 through A20 enter:

=RAND()

this is the data we must transform to meet our requirements. In B1 enter:

=A1*0.005/STDEVP(A:A)

and copy down through B20. The values in column B will have the desired std-dev.

In C1 enter:

=B1+7-AVERAGE(B:B)

and copy down through C20.

Column C will have both the correct std-dev and correct mean:

enter image description here

With VBA:

Perform exactly the same technique, but use arrays rather than columns of cells. It is equally easy to generate sample data for Poisson, LogNormal, etc. distributions. Here is some simple code to put 20 values in column G:

Sub ForcedDistribution()
    Dim ary(1 To 20) As Double
    Dim bry(1 To 20) As Double
    Dim cry(1 To 20) As Double
    Dim mean As Double, std As Double
    Dim i As Long, wf As WorksheetFunction
    Set wf = Application.WorksheetFunction

    Randomize
    mean = 7#
    std = 0.005

    For i = 1 To 20
        ary(i) = Rnd
    Next i

    For i = 1 To 20
        bry(i) = ary(i) * std / wf.StDevP(ary)
    Next i

    For i = 1 To 20
        cry(i) = bry(i) - wf.Average(bry) + mean
    Next i

    For i = 1 To 20
        Cells(i, 7).Value = cry(i)
    Next i
End Sub

Upvotes: 1

Related Questions