- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
New to JMP and struggling with Standard Deviation differences between Excel & JMP
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 | * |
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: New to JMP and struggling with Standard Deviation differences between Excel & JMP
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
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: New to JMP and struggling with Standard Deviation differences between Excel & JMP
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: New to JMP and struggling with Standard Deviation differences between Excel & JMP
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.
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.
Appreciate your help so far and looking how I can further make sense of JMP!
Chris
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: New to JMP and struggling with Standard Deviation differences between Excel & JMP
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
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: New to JMP and struggling with Standard Deviation differences between Excel & JMP
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: New to JMP and struggling with Standard Deviation differences between Excel & JMP
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):
Cheers, Jordan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: New to JMP and struggling with Standard Deviation differences between Excel & JMP
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: New to JMP and struggling with Standard Deviation differences between Excel & JMP
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: New to JMP and struggling with Standard Deviation differences between Excel & JMP
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: New to JMP and struggling with Standard Deviation differences between Excel & JMP
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.