Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have been trying to develop an I-MR control chart. I've found several useful threads, but am still having an issue. I have my data in a table that is similar to the below:
Wave | Candidate | Date Trained | Date Certified |
---|---|---|---|
2014 Wave 1 | A | 3/20/2014 | 10/15/2014 |
2014 Wave 1 | B | 3/20/2014 | |
2014 Wave 1 | C | 3/20/2014 | 12/16/2014 |
2014 Wave 1 | D | 3/20/2014 | 11/25/2014 |
2014 Wave 2 | E | 6/5/2014 | 5/7/2015 |
2014 Wave 2 | F | 6/5/2014 | |
2014 Wave 2 | G | 6/5/2014 | 12/16/2014 |
2014 Wave 2 | H | 6/5/2014 | 11/25/2014 |
2015 Wave 1 | I | 2/5/2015 | 8/20/2015 |
2015 Wave 1 | J | 2/5/2015 | 7/13/2015 |
2015 Wave 1 | K | 2/5/2015 | 9/17/2015 |
My data points are the days between the two dates, so I am actually looking for the moving range between the days. However, I have to have the blank cells as those candidates have not certified. Is there a way to calculate the moving range of just the data that has a date in 'Date Certified' and ignore the null cells?
I have tried this several different ways, but cannot get it to work. What I want is similar to the below:
Days to Certify | Range |
---|---|
209 | |
271 | 62 |
250 | 21 |
336 | 86 |
194 | 142 |
196 | 2 |
158 | 38 |
224 | 66 |
I would appreciate any help with this. When I try I either get a blank or a very large value because it is calculating the blank cells as well.
Thanks!
Ali
See attached example.
Thank you Gysbert. I tried your solution and had two issues. First, using just the 'Days to Certify' expression, no values populated in the table. Then, when I added the 'Range' expression I got an error in expression of ')' expected. When I put in the 'Days to Certify' as part of the 'Range' expression, the 'Days to Certify' is blue instead of red. Could this be part of my issue?
Thanks for your help!