Control Chart Graph conflicting with data points

[Jacked0528]

Good Afternoon All,

I created a dynamic control chart from a video assist a number of months ago and the dynamic chart in and of itself works great. The only issue is the chart is designed to be used when there's an Upper and Lower Spec as well as an Upper and Lower Control limit. Once you start pulling any of those 4 data points away it gets weird. There's plenty of times when I won't have all 4 criteria defined so I need the graph to work and work well if even only 1 is populated. I'll try and upload the mini-sheet this evening after trying to download the L2BB extension but I suppose to get my initial thoughts out I'll upload images/issues I'm struggling with (maybe the pictures will help when the formulas are available anyways).

Issue 1: No matter what configuration of blanks, zeroes, etc. I use, if all 4 data points aren't listed (USL, LSL, UCL, LCL) the remainder hang out at 0.0000 on the graph.

Image 1 - There's only a lower control limit but on the graph all data points are flagged as control limit violations (Formula says it's greater than the UCL so it's a violation but telling it to ignore blanks, zeroes, etc. just throw error messages)
Image 2 - Once I apply "-" to Upper Control Limit I get the reference error message (note it didn't give me the message when I had the "-" in the Upper Spec Limit (image1))

There may be a few others but I'll start with those as some of the fix opportunities could fix the others.

 

[awoohaw]

While waiting for the xl2bb data, this may help:
For all the places where you have blanks, what happens if you put =NA() function as result of the calculation in those "" (or blank) cells? (this works in on line charts by not putting a line between the two points on either side (or just one side if it is the first or last of the series).

 

[AhoyNC]

If you don't care about an (USL, LSL, UCL, LCL), may be just enter a bogus number for the missing value, so you won't get an out of spec. Another other option may be to have Excel calculate the missing value(s) from the data entered. Doesn't your customer have LSL and USL they want you to meet?

 

[Jacked0528]

When I try uploading the mini sheet it doesn't bring the graph even though it's inside the selected cell range. How can I incorporate that into this, or is that not necessary at this point?

 

[Jacked0528]

If you don't care about an (USL, LSL, UCL, LCL), may be just enter a bogus number for the missing value, so you won't get an out of spec. Another other option may be to have Excel calculate the missing value(s) from the data entered. Doesn't your customer have LSL and USL they want you to meet?

I'd like the form to be robust enough to capture data when only minimum or maximum data points are required which sometimes is a requirement. As a last resort I could have the UCL/LCL match the USL/LSL but that doesn't work in all variations so I was hoping to have everyone here peer into the workings to see if each individually can work successfully before creating a bunch of exceptions (to only have USL you need to do x, y and z to have it work, etc.).

 

[awoohaw]

When I try uploading the mini sheet it doesn't bring the graph even though it's inside the selected cell range. How can I incorporate that into this, or is that not necessary at this point?

xl2bb doesn't copy chart objects (in my experience) on the sheets. So, an image of the chart is okay. But, for the forum to really help you we should get your data that builds the chart.

 

[Jacked0528]

Let's give this one a shot. I deleted the data points for measurements 21-100 but they plot on the graph no different than 1-20.

SPC Form_Variable_Min Only Example.xlsx
	A	B	C	D	E	F	G	H	I	J												V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
3	1	Nomenclature					Widget																												2	Lot/Batch No.				ABC123
4
5	Characteristic																					3	.010			4	Depth
6
7	7	Upper Spec Limit																					-		inches			9	Upper Control Limit									inches
8
9	8	Lower Spec Limit																					0.0100		inches			10	Lower Control Limit						0.0130			inches
10
11	16	Unit		Reading	LSL	LCL	UCL	USL	CL Alert	SL Alert													Unit		Reading				Unit		Reading				Unit		Reading				Unit		Reading
12		1		0.0150	0.0100	0.0130	0.0000		0.0150	#N/A													21						41						61						81
13		2		0.0120	0.0100	0.0130	0.0000		0.0120	#N/A													22						42						62						82
14		3		0.0170	0.0100	0.0130	0.0000		0.0170	#N/A													23						43						63						83
15		4		0.0130	0.0100	0.0130	0.0000		0.0130	#N/A													24						44						64						84
16		5		0.0135	0.0100	0.0130	0.0000		0.0135	#N/A													25						45						65						85
17		6		0.0170	0.0100	0.0130	0.0000		0.0170	#N/A													26						46						66						86
18		7		0.0150	0.0100	0.0130	0.0000		0.0150	#N/A													27						47						67						87
19		8		0.0160	0.0100	0.0130	0.0000		0.0160	#N/A													28						48						68						88
20		9		0.0140	0.0100	0.0130	0.0000		0.0140	#N/A													29						49						69						89
21		10		0.0150	0.0100	0.0130	0.0000		0.0150	#N/A													30						50						70						90
22		11		0.0130	0.0100	0.0130	0.0000		0.0130	#N/A													31						51						71						91
23		12		0.0170	0.0100	0.0130	0.0000		0.0170	#N/A													32						52						72						92
24		13		0.0160	0.0100	0.0130	0.0000		0.0160	#N/A													33						53						73						93
25		14		0.0170	0.0100	0.0130	0.0000		0.0170	#N/A													34						54						74						94
26		15		0.0150	0.0100	0.0130	0.0000		0.0150	#N/A													35						55						75						95
27		16		0.0190	0.0100	0.0130	0.0000		0.0190	#N/A													36						56						76						96
28		17		0.0170	0.0100	0.0130	0.0000		0.0170	#N/A													37						57						77						97
29		18		0.0150	0.0100	0.0130	0.0000		0.0150	#N/A													38						58						78						98
30		19		0.0160	0.0100	0.0130	0.0000		0.0160	#N/A													39						59						79						99
31		20		0.0170	0.0100	0.0130	0.0000		0.0170	#N/A													40						60						80						100
Sheet 1

Cell Formulas
Range	Formula
AL7	AL7	=Y7
Y9	Y9	=Y7
AL9	AL9	=Y7
E12:E31	E12	=IF(OR(D12="",$W$9="-"),"",$W$9)
F12:F31	F12	=IF(OR(D12="",$AI$9="-"),"",$AI$9)
G12:G31	G12	=IF(OR(D12="",$AI$7="-"),"",$AI$7)
H12:H31	H12	=IF(OR(D12="",$W$7="-"),"",$W$7)
I12:I31	I12	=IF(AND(ISERROR(J12),OR(D12<F12,D12>G12)),D12,#N/A)
J12:J31	J12	=IF(OR(D12<E12,D12>H12),D12,#N/A)

Named Ranges
Name	Refers To	Cells
'Sheet 1'!LCL	=OFFSET('Sheet 1'!$F$12,,,COUNT('Sheet 1'!$G$12:$G$111))	I12
'Sheet 1'!LSL	=OFFSET('Sheet 1'!$E$12,,,COUNT('Sheet 1'!$G$12:$G$111))	J12
'Sheet 1'!Reading	=OFFSET('Sheet 1'!$D$12,,,COUNT('Sheet 1'!$G$12:$G$111))	E12:J12
'Sheet 1'!SLAlert	=OFFSET('Sheet 1'!$J$12,,,COUNT('Sheet 1'!$G$12:$G$111))	I12
'Sheet 1'!UCL	=OFFSET('Sheet 1'!$G$12,,,COUNT('Sheet 1'!$G$12:$G$111))	I12
'Sheet 1'!USL	=OFFSET('Sheet 1'!$H$12,,,COUNT('Sheet 1'!$G$12:$G$111))	J12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AV12:AW22,Y12:Y31,AE12:AE31,AK12:AK31,AQ12:AQ31,BD19:BD20,AV23,AV24:AW111,AB32:AB111,AH32:AH111,AN32:AN111,AT32:AT111,AJ112,AP112,AU112,AD112:AD113,AX112:AX113,H113:I113,D12:F112	Cell	contains a blank value	text	YES
D12	Expression	=$D$12=0	text	NO
D12:D112,Y12:Y31,AE12:AE31,AK12:AK31,AQ12:AS31	Expression	=(D12>$W$9)*AND($W$7="-")	text	YES
E1:AJ1,W9	Cell	contains a blank value	text	NO
AN3:AT3	Cell	contains a blank value	text	NO
Y12:Y31,AE12:AE31,AK12:AK31,AQ12:AS31,D12:D112	Expression	=(D12<$W$7)*AND($W$9="-")	text	YES
G3:AH3,BA3:BH3,BB5:BE5,BD10:BF10	Cell	contains a blank value	text	NO
W7	Cell	contains a blank value	text	NO
AA5	Cell	contains a blank value	text	NO
AI7:AK7,AI9:AK9	Cell	contains a blank value	text	NO
W5,BD9,BB114	Cell	contains a blank value	text	NO
D12:D31,Y12:AA31,AE12:AG31,AK12:AM31,AQ12:AS31	Expression	=$W$9="-"	text	YES
Y12:Y31,AE12:AE31,AK12:AK31,AQ12:AS31,D12:D112	Cell Value	<$W$9	text	NO
D12:D31,Y12:AA31,AE12:AG31,AK12:AM31,AQ12:AS31	Expression	=$W$7="-"	text	YES
Y12:Y31,AE12:AE31,AK12:AK31,AQ12:AS31,D12:D112	Cell Value	>$W$7	text	NO

Cells with Data Validation
Cell	Allow	Criteria
Y7	List	=$Q$172:$Q$173

 

[Jacked0528]

Here is a screenshot of the graph represented by the mini sheet above.