Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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....... |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Normal Distribution? | Excel Discussion (Misc queries) | |||
watch a curve change over time like a movie | Charts and Charting in Excel | |||
Vertical ND Curve on Combination Chart | Charts and Charting in Excel | |||
bell-shape normal distribution curve | Charts and Charting in Excel | |||
How do I make a Normal Curve with my data | Setting up and Configuration of Excel |