VBA module/function to generate random numbers with specified mean and SD

  • Thread starter Thread starter Robert Reid
  • Start date Start date
R

Robert Reid

Hi all,

As basis for a school assignment I need to generate 20 random numbers that
have a prespecified mean and standard deviation ( I don't believe they have
to be normally distrubted).

Can anyone help?

The output would preferably be to Worksheets("Sheet1").Range("A1:A20")
though an array would also work.

Thanks.
 
Thanks for the response Mike,

I'm not sure how I would use the excel function to generate 20 numbers that
have a prespecified SD and mean using VBA.

Can you clarify.
 
Robert Reid -

The worksheet functions =NORMINV(RAND(),mean,stdev) with appropriate
numerical values for mean and stdev entered in worksheet cells will generate
such numbers.

If for some reason you must use VBA, either (1) write VBA code that enters
the above into cells or (2) write VBA code with
Application.WorksheetFunction.NormInv and VBA's RND function to generate the
values and then write VBA code to insert the values into worksheet cells.

- Mike
 
With Worksheets("Sheet1").Range("A1:A20")
.formula = "=NORMINV(RAND(),150,10)"
.Formula = .Value
End With
 
Thanks to you all.


Robert Reid said:
Thanks for the response Mike,

I'm not sure how I would use the excel function to generate 20 numbers
that have a prespecified SD and mean using VBA.

Can you clarify.
Click to expand...
 
Back
Top