New to JMP and struggling with Standard Deviation differences between Excel & JMP

[#1]

I'm a 20+ year user of Minitab but my company is no longer buying the licenses because of recent drastic price increases and licensing model.  Just got JMP version 17.0.0 on Friday and trying to figure out how to do some Control Charts.  My biggest issue is trying to understand how JMP is giving me a different Standard Deviation than by my manually calculating them in Excel.

When I calculate the Mean on the Data Value Column (Data Below), both Excel and JMP come up with 8.9259... 

When I calculate the Standard Deviation =STDEV.P(), Excel gives me a StDev of 7.6880 resulting in an UCL limit of 31.19.   In JMP, it is calculating an UCL of 17.88881.   What am I not doing right / considering correctly?   

This is supposed to be an IMR chart in JMP but it keeps building a C Chart so not sure if that has something to do with it. 

I like that JMP is automatically bounding the LCL at 0 since it can't be a negative.

The data points with an * are obviously "No Observed Data" at this time and in excel, I have it inject an * in the calculations to get the data.

Look forward to any insights you can provide.

Thank you!

Record	Data
1	28
2	12
3	9
4	5
5	13
6	22
7	36
8	20
9	9
10	6
11	19
12	7
13	13
14	2
15	13
16	9
17	17
18	30
19	*
20	*
21	*
22	*
23	15
24	*
25	4
26	*
27	9
28	7
29	13
30	5
31	8
32	4
33	8
34	13
35	4
36	*
37	1
38	6
39	2
40	6
41	*
42	1
43	3
44	9
45	1
46	*
47	7
48	1
49	9
50	*
51	14
52	14
53	*
54	*
55	1
56	2
57	2
58	14
59	7
60	9
61	2
62	*
63	1
64	*
65	6
66	*
67	*
68	1
69	*
70	0
71	3
72	*
73	*
74	*
75	*
76	*
77	*
78	*
79	*

[#2]

Hi @ChrisMarkley ,

  Welcome to the JMP discussion pages. I hope you're enjoying the switch over to JMP.

  Regarding your question about standard deviations, I believe the issue is that in Excel, you're using the population to calculate the standard deviation. JMP is using the sample standard deviation, so it uses a denominator that is (N-1) rather than N. In Excel, if you instead use =STDEV.S() you'll get the same answer as JMP.

  As for the control chart, have you tried using the Control Chart Builder? Or tried to actually choose IMR Chart from the Analyze > Quality and Process > Control Chart menu?

  However, here, I think the reason why JMP is not automatically doing an IMR chart is because your Data column is likely being treated as a Nominal data type. When you have the asterisks (*) as a data entry in JMP, JMP treats the column as text. If you replace all the *'s with a period (.) and then change the column data type back to Numeric and Continuous, you should be able to get the IMR chart as desired. But yes, depending on the chart type, the LCL and UCL will be calculated differently.

Hope this helps!,
DS

[#3]

DS,

Thank you for responding!

JMP properly converted the * into a dot as if I entered a Period marker so don't think that is it.

First learning here.  Having to "code" the data as Nominal vs. Continuous is new to me.  Figured out how to make that change and it is closer in calculation and it actually allowed me this time to calculate an Upper CL that is closer to what I'm seeing in Excel.

In Excel, I chose STDEV.P because this is the full population of Data at this time and I'm not looking at thousands or rows of data.  If I choose STDEV.S, then my UCL actually increases.

In Excel, here's the outputted chart using STDEV.S where I've obfuscated the titles, labels etc.  UCL is now 32.21 and I've bounded the LCL at 0.

ChrisMarkley_0-1701716556120.png

Now that JMP is calculating an IMR Chart (Thank you) you can see that the UCL is ~6 off from Excel so still trying to figure out what I'm not doing right.

ChrisMarkley_1-1701716868911.png

Appreciate your help so far and looking how I can further make sense of JMP!

Chris

[#4 · Accepted solution]

Do you know which formula is being used in excel? JMP does offer few different options for you from Limits part and Levey Jennings seems to provide similar UCL

jthi_0-1701718327538.png

Edit:

When you have platform open in JMP, select ? tool from toolbar and click on the platform. It will open Help page for you regarding that platform so you can easily explore related documentation ( Quality and Process Methods > Control Chart Builder > Launch Control Chart Builder > Control Chart ... ). From the help page you can also find Statistical Details... parts for the platforms which can be helpful

jthi_1-1701718628311.png

[#5]

JTHI,

Thank you for the response.

In my Excel workbook, I'm calculating UCL by:

UCL = MEAN VALUE + 3x STANDARD DEVIATION

LCL = MEAN VALUE - 3x STANDARD DEVIATION

Again learned something new and am going to have to figure out what Levey Jennings calculations are.

[#6]

Hi Chris,

Please check out this presentation on the problems associated with using the sample standard deviation for your control limits (aka Levey Jennings charts):

https://community.jmp.com/t5/Discovery-Summit-Americas-2023/Friends-Don-t-Let-Friends-Misuse-Levey-J...

Cheers, Jordan

[#7]

I have seen several people hint at this, but I did not see it stated outright: An ImR or XmR chart is calculating the control limits by using the average of the moving range, NOT the standard deviation (hence the name of ImR). Therefore, you should expect differences from what you calculated in Excel. The average of the moving ranges (times an appropriate constant) should be close to the standard deviation, but it will not be exact.

[#8]

Hi @ChrisMarkley 

To add-on @Jordan_Hiller , @Dan_Obermiller , @SDF1 @statman  ;

depends on which formula that you input in Excel (which Excel version?) to calculate std deviation, you may/may not see similar result in JMP.

I've summarized the comparison between Excel & JMP17.0 (using Control Chart Builder) for your reference.

STDEV or STDEV.S in Excel is similar to Levey-Jennings sigma (which is same with std dev that you will get from Distribution platform)

WebDesignesCrow_0-1701766821736.png

[#9]

Hi @ChrisMarkley ,

  I would do as Jarmo suggested and get the help page for Control Chart Builder to review the different kind of control charts that are available and get know how the different charts compute the different control limits, etc. And, changing the sigma for the Limits[1] in CCB as Jarmo pointed out will change it so that it is using the overall sigma to calculate the limits and you get what you expect from your Excel file. If you really want to calculate the population standard deviation, you can do that by multiplying the standard deviation that JMP calculates by sqrt((N-1)/N), where N=54 in this case -- as you have 54 non missing values in your column Data.

  If you don't like how the CCB platform creates your CC, you can always make your own version with graph builder.

Hope this helps!,

DS

[#10]

Hi @ChrisMarkley : So this doesn't get lost; you said "In Excel, I chose STDEV.P because this is the full population of Data at this time and I'm not looking at thousands or rows of data. ".  It sounds like you are confusing the Population with "all the data I have". They are not the same thing. I never say never...but I can't imagine a situation in the real world of Statistical Inference (as different from summarizing data via calculating statistics) that you'd use the population standard deviation. In Statistical Inference, we never know the population. 

i.e., presumably, you could have collected more data...but you didn't; in theory, you could have collected an infinite amount of data (but can't due to time/resources/etc); that "infinity" is the population.  What you've done is taken a sample (n=54) from that infinite population.