What is the formula to create a bell curve?

M

mi06rasja

#1
Hi

Iam trying to make my self independent from any statistical software program by making my own, using excel and macro. However, now Iam stuck trying to figure out how minitab construct their bell shape curve in there capability chart.

My assumption is that the formula for the bell shape cureve is the same as the formula for the normal distribution i.e.

f(x)=(1/sigma*sqrt(2*Pi))*e^(-(x-my)^2/2*sigma^2)

But in my case I use my own calculated sigma (within and overal). I also use Xbarbar as my my. At last I use diferent x values starting a couple of steps downstream from my Xbarbar and stops the same amount of steps higher from Xbarbar until f(x)=0,001.

However, when I make a chart using this procedure I do get a bell shape curve but this curve dosn't have the same width (deviation) as the one I compere with in minitab. My bell shape curve is really narrow and pointy in comparrison with minitab. What am I doing wrong?
 
Elsmar Forum Sponsor

Steve Prevette

Deming Disciple
Staff member
Super Moderator
#3
Best thing to do is use the built-in Excel functions. Hit the Fx button, and type in "normal" in the search box and it will give you the various functions available.
 
M

mi06rasja

#4
Hi

Thanks for your response. The only problem with this soulution is that i dont know whitch value of x i should use. The code in my macro is buildt in a way that the program starts at Xbarbar and checks wether or not (the y value in the bell curve) I am below a certain number, in my case 0,001. The program then goes backward until Iam below that number and counts every loop. I then double the value for the loop counter and use that counter in order to start writing each value. Later on i make a diagram using this data.

In my head it should work fine but Iam having some problem with the bell curve value.

I don?t really know what Iam doing wrong? Is the bell shape curve in minitabs capability diagram the same as the one I have seen in my statistical book of Normal distribution? The value of x in that formula does that have any connection to the samples i have gathered or is it, as I think it is, just ranged values near the Xbarbar?


c8 = (Application.Max(Sheets("Measurements").Range("B3:B" & RowCount)) - Application.Min(Sheets("Measurements").Range("B3:B" & RowCount))) / (RowCount * 100)

x = Xbarbar
c10 = 1
Do Until (1 / (StdDev_within * (2 * Pi) ^ 0.5)) * Exp(-((x - c8 * c10) - Xbarbar) ^ 2 / 2 * StdDev_within ^ 2) < 0.001
c10 = c10 + 1
Loop

x = x - c8 * c10
c9 = 0
For c11 = 1 To c10 * 2
Sheets("Data").Cells(c9 + 15, 14) = (1 / (StdDev_overal * (2 * Pi) ^ 0.5)) * Exp(-(x - Xbarbar) ^ 2 / 2 * StdDev_overal ^ 2)
Sheets("Data").Cells(c9 + 15, 15) = x
c9 = c9 + 1
x = x + c8
Next c11
 

Steve Prevette

Deming Disciple
Staff member
Super Moderator
#5
My question is - what is your question? Is it in relation to plotting a bell curve, or is it in respect to control chart formulae in Minitab?

My preference is to separate the two issues.
 
Last edited:
M

mi06rasja

#6
My question is : What formula does Minitab use to create their bell curve in their plot capability analyzis normal? Because I would like to create my own.
 
D

Darius

#8
I think the issue here is how to chart a normal distribution, mi06rasja, you are wrong about the equation:

(1 / (StdDev_within * (2 * Pi) ^ 0.5)) * Exp(-((x - c8 * c10) - Xbarbar) ^ 2 / 2 * StdDev_within ^ 2)

or the same with Overall

(1 / (StdDev_within * (2 * Pi) ^ 0.5)) * Exp(-((x - c8 * c10) - Xbarbar) ^ 2 / 2 / StdDev_within ^ 2)

Das ist rightig!

Your code, or the code you share looks like taken from somewhere, strange that it is not completed:confused:, if you wanted help is weir to give something with missing parts.

:magic:I recoded and put an example on an excel file, but you need to abilitate the macros on Excel. By the way, capability deals with within standard deviation, so it's not logical to use Overall. In the code I added the within variation estimate but for individuals, you have to change it, if you want if for subgroups.
 

Attachments

Last edited by a moderator:
D

Darius

#9
Well for the ones that don't like to open excel files with macros, there is the code:magic:

Code:
Sub Bell_Chart()
'initializations
Pi = Evaluate("Pi()")
Ac = 0
RW = 4
'obtain variation estimate (for the example is Moving Range estimate) and data's average
Do
  Ac = Ac + Abs(Cells(RW, 2) - Cells(RW - 1, 2))
  RW = RW + 1
Loop Until Cells(RW, 2) = ""
RowCount = RW - 3
Xbarbar = Evaluate("Average(B3:B" & RowCount + 2 & ")")
StdDev_within = Ac / (RowCount - 1) / 1.128
'obtain the limits for the bell shape, the X where the accumulated norm dist is 0.001 and 0.999
Min = Evaluate("NORMINV(0.001," & Xbarbar & "," & StdDev_within & ")")
Max = Evaluate("NORMINV(0.999," & Xbarbar & "," & StdDev_within & ")")
'so, I have the Min and Max of the chart, but if the data has values beyond these values, adjust the chart limits
If Min > Cells(4, 5) Then Min = Cells(4, 5)
If Max < Cells(5, 5) Then Max = Cells(5, 5)
Ac = (Max - Min) / 10
'and add a little something so the chart won't be touching the borderline (the limits of the chart)
Min = Min - Ac: Max = Max + Ac
'calculate the Cpk (for moving range), this wasn't in the original code, but it's nice, if you put the LSL in cell(4,5) and USL in cell(5,5)
Cells(6, 5) = (Xbarbar - Cells(4, 5)) / (3 * StdDev_within)
Ac = (Cells(5, 5) - Xbarbar) / (3 * StdDev_within)
If Cells(6, 5) > Ac Then Cells(6, 5) = Ac
'a difference against the posted code, I will always take 100 data points to make the chart
x = Min
c8 = (Max - Min) / 100
For RW = 15 To 115
'you can use the commented equation insteed, like Steve said, but I used yours with a slight change
'Sheets("Data").Cells(RW, 14) = Evaluate("NormDist(" & x & "," & Xbarbar & "," & StdDev_within & ",false)")
Sheets("Data").Cells(RW, 14) = (1 / (StdDev_within * (2 * Pi) ^ 0.5)) * Exp(-((x - Xbarbar) / StdDev_within) ^ 2 / 2)
Sheets("Data").Cells(RW, 15) = x
'I added the next 2 instructions for charting the LSL and USL
Sheets("Data").Cells(RW, 16) = Cells(4, 5)
Sheets("Data").Cells(RW, 17) = Cells(5, 5)
x = x + c8
Next RW
'this doesn't need to be done always, but once done it will make the charting more easily, insteed of ranges defined on the chart can be their names.
Sheets("Data").Range(Sheets("Data").Cells(15, 14), Sheets("Data").Cells(115, 14)).Name = "X_Values"
Sheets("Data").Range(Sheets("Data").Cells(15, 15), Sheets("Data").Cells(115, 15)).Name = "Bell"
Sheets("Data").Range(Sheets("Data").Cells(15, 16), Sheets("Data").Cells(115, 16)).Name = "LSL"
Sheets("Data").Range(Sheets("Data").Cells(15, 17), Sheets("Data").Cells(115, 17)).Name = "USL"
End Sub
 
Thread starter Similar threads Forum Replies Date
R Cost per test tool/formula Oil and Gas Industry Standards and Regulations 1
R Cost per test tool/formula Oil and Gas Industry Standards and Regulations 1
S Bend allowance formula to calculate per degree per radii Manufacturing and Related Processes 2
J Unbiasing Constant "d4" formula Statistical Analysis Tools, Techniques and SPC 4
S Formula for Calculating NoGo Major Diameter for UN gages Inspection, Prints (Drawings), Testing, Sampling and Related Topics 4
A Excel OOT (Out of Tolerance) Formula Excel .xls Spreadsheet Templates and Tools 3
J CMM Accuracy Formula General Measurement Device and Calibration Topics 3
I Formula in the Annex of IEC 61000-4-3 - How is k=7 determined? IEC 60601 - Medical Electrical Equipment Safety Standards Series 2
Z Inspection Form With Pass/Fail Formula Excel .xls Spreadsheet Templates and Tools 11
S used excel formula calculated the ARL's with Rule1&Rule2 for Shewhart control chart Statistical Analysis Tools, Techniques and SPC 0
A Cpk Formula seems off, need help!? Capability, Accuracy and Stability - Processes, Machines, etc. 11
L Signal-to-Noise (S/N) Ratio formula check Design and Development of Products and Processes 2
S Scrap % Calculation Formula Manufacturing and Related Processes 10
P IF formula - Excel 2003 Excel .xls Spreadsheet Templates and Tools 4
J Bias formula in MSA 4th Edition vs 3rd Edition Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 4
N Formula that will calculate the NoGo Major Diameter of an M20 x 1.5 6H Thread gage Inspection, Prints (Drawings), Testing, Sampling and Related Topics 3
R Formula to tie together - Confidence/Reliability Level AQL? LTPD? Reliability Analysis - Predictions, Testing and Standards 3
F How to Calculate the unbiased Standard Deviation in Excel using Minitab Formula Capability, Accuracy and Stability - Processes, Machines, etc. 6
M Dimension Report Formula .xlsx Editable Spreadsheet Form Excel .xls Spreadsheet Templates and Tools 24
M Should I use binomial or Poisson distribution formula? Statistical Analysis Tools, Techniques and SPC 6
F Excel I want to use the count I get in another formula Excel .xls Spreadsheet Templates and Tools 13
M Formula for Bell Shape in Excel Using Minitab Software 1
P Does anyone have the GRR Formula? Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 1
M Standard Deviation Estimation Formula - Student Question Statistical Analysis Tools, Techniques and SPC 4
B Formula Data Validation Set for Cp, Cpk, Pp & Ppk Capability, Accuracy and Stability - Processes, Machines, etc. 1
B Attrition Rate and the formula to calculate Attrition Rate Quality Manager and Management Related Issues 1
D Demonstration Reliability Test Formula with allowance for 'x' Failures Reliability Analysis - Predictions, Testing and Standards 6
N Is there a formula to link words in Excel sheet to a graph? Excel .xls Spreadsheet Templates and Tools 5
N Linear Regression Formula with Four Variables to a Power Regression Formula Using Minitab Software 2
M MSA - According to the formula cg how much tolerance should be put in place? Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 1
F SPC Newbie looking for Excel Spreadsheet formula for a sample size of 40 components Capability, Accuracy and Stability - Processes, Machines, etc. 8
A Bias Linearity Stability MSA Excel File with Formula Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 4
J Repeatability Standard Deviation Formula in MSA 4th Edition Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 5
M Formula for Prioritizing Engineering Change Requests (ECR)? Document Control Systems, Procedures, Forms and Templates 1
I AQL Formula in Excel - Calculating Sample Size based on an AQL Chart AQL - Acceptable Quality Level 8
D Formula for Calculating Warp Measurement of Corrugated Boxes General Measurement Device and Calibration Topics 1
Z KPI Formula Issue - "Change Requests Managed" = Changed/Total ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 7
O Which role plays Inverse cdf of a Standard Normal Distribution in Formula for Z-Bench Using Minitab Software 8
D Route Delivery Efficiency Formula - Miles driven, number of deliveries, and time(hrs) Benchmarking 3
S Formula used in Minitab 16 - "Pooled Standard Deviation" Statistical Analysis Tools, Techniques and SPC 2
X Microsoft Excel 2003 Formula Restrictions Excel .xls Spreadsheet Templates and Tools 9
T Gage R&R Sheet Formula error?! NDC less than 4 Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 3
C Sample Size Calculation Formula for Process Capability Statistical Analysis Tools, Techniques and SPC 25
Q Accuracy: Is there a specific formula to compute Measurement Device Accuracy? Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 3
Rameshwar25 Manual Formula for p-value Calculation for MSA Analysis Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 10
D Ring Gauges P.D Formula using "Ball Rounded Teeth" General Measurement Device and Calibration Topics 13
R Formula for calculating Money Value equivalent to 1% FPY (First Pass Yield) Six Sigma 5
B Problem with Reliability and Availability Formula Reliability Analysis - Predictions, Testing and Standards 8
N Usability Formula to Predict Use Error Reduction IEC 62366 - Medical Device Usability Engineering 6
S Linearity Calculation Worksheet (including formula) General Measurement Device and Calibration Topics 1

Similar threads

Top Bottom