Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Ali Baba
 
Posts: n/a
Default 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.......

  #3   Report Post  
Ali Baba
 
Posts: n/a
Default

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



  #4   Report Post  
Tushar Mehta
 
Posts: n/a
Default

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




  #5   Report Post  
Ali Baba
 
Posts: n/a
Default

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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Normal Distribution? Anthony Slater Excel Discussion (Misc queries) 2 March 8th 05 08:21 PM
watch a curve change over time like a movie RandyBarrett Charts and Charting in Excel 6 February 19th 05 06:00 AM
Vertical ND Curve on Combination Chart Phil Hageman Charts and Charting in Excel 4 December 30th 04 07:07 PM
bell-shape normal distribution curve SM Charts and Charting in Excel 6 December 11th 04 09:29 PM
How do I make a Normal Curve with my data OJ Setting up and Configuration of Excel 0 December 5th 04 05:05 AM


All times are GMT +1. The time now is 10:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"