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

[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.

 

[Robert Reid]

BTW - the generated numbers have to be >= 0 (positive numbers)

 

[Mike Middleton]

Robert Reid -

=NORMINV(RAND(),mean,stdev)

Caveat: Both NormInv and Rand may return numerically inaccurate results in
pre-2003 Excel.

- Mike
www.mikemiddleton.com

 

[Robert Reid]

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.

 

[Mike Middleton]

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

 

[Tom Ogilvy]

With Worksheets("Sheet1").Range("A1:A20")
.formula = "=NORMINV(RAND(),150,10)"
.Formula = .Value
End With

 

[Robert Reid]

Thanks to you all.

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.