Plotting normal distribution curve
 

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/jp...1124104974.jpg
Please Help me if you can.......

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

In article ,
says...
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/jp...1124104974.jpg
Please Help me if you can.......

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" wrote:

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

In article ,
says...
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/jp...1124104974.jpg
Please Help me if you can.......

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/ch...tion/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

In article ,
says...
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" wrote:

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

In article ,
says...
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/jp...1124104974.jpg
Please Help me if you can.......

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

"Tushar Mehta" wrote:

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/ch...tion/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

In article ,
says...
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" wrote:

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

In article ,
says...
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/jp...1124104974.jpg
Please Help me if you can.......