Plotting normal distribution curve

[Guest]

Hi

I am trying to plot a normal distribution curve using vba. i think there is
a way to use loops to generate the x and hence y values.


I understand that we use
Application.WorksheetFunction.NormDist() function to get the y-values
and we use loops to generate our x-values

so what i really want is to evaluate the normal distribution function at
different values of x to enable me to plot the graph.

In the chart I used Offset function
XVals =OFFSET(Sheet1!$E$2,0,0,Sheet1!$B$5)
YVals = =OFFSET(XVal,0,1)


This is illustration of what I want
http://phpaw.com/myscrpit/milt-up/jpg/jpg-1124104974.jpg
Please Help me if you can.......

 

[Tushar Mehta]

So what do you need help with? You appear to have the correct plan.
Just go ahead and implement it. If you get stuck on a particular issue
post back and someone should be able to assist you.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

You did not get me

I meant that I only need to enter the mean, standard deviation and the
range of x values to get the normal distribution curve.

I am new to VBA so I don't know how to write the code

I know this is an easy task to anybody who knows VBA well.

 

[Tushar Mehta]

Actually, it is not as easy as one might imagine with VBA especially
since XL imposes rather stringent limits on the length of a chart's
series formula. You are much better off transfering the results on any
analysis into a worksheet range and plotting that range.

For a few different ways to create a normal distribution see
Normal curve
http://www.tushar-mehta.com/excel/charts/normal_distribution/index.htm

You should be able to use the macro recorder (TOols | Macro > Record
new macro...) with either of the first 2 methods on that page.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

many thanks Tushar, this is actually what I wanted

Private Sub CommandButton1_Click()

Dim mu As Variant
Dim segma As Variant
Dim xfirst As Variant
Dim xlast As Variant
Dim Nstep As Long
Dim X As Variant
Dim y As Variant
Dim lngRow As Long
Dim stepvalue As Variant


' enter values of mu, segma, xfirst, xlast, Nstep
mu = Sheets(1).Range("B1")
segma = Sheets(1).Range("b2")
xfirst = Sheets(1).Range("b3")
xlast = Sheets(1).Range("b4")
Nstep = Sheets(1).Range("b5")

stepvalue = (xlast - xfirst) / (Nstep - 1)

lngRow = 0
X = xfirst

With Range("d1")
Do While X <= xlast
.Offset(lngRow, 0) = X
y = Application.WorksheetFunction.NormDist(X, mu, segma, False)
.Offset(lngRow, 1) = y
X = X + stepvalue
lngRow = lngRow + 1
Loop
End With

End Sub

I managed to write it by trial and error

Many thanks anyway
My question was not clear