What is the formula to create a bell curve?

M

mi06rasja

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?
 

Steve Prevette

Deming Disciple
Leader
Super Moderator
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

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
Leader
Super Moderator
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

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

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

  • NiceCharts.xls
    86.5 KB · Views: 275
Last edited by a moderator:
D

Darius

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
 
Top Bottom