Hi All

I need a bit of help with a (I think) complicated excel problem.

I am using excel to help keep track of process capability studies
(Checking the capability of a manufacturing process for those that
don't know)

What I have is a drawing dimmension (in this case a diameter) that I
have to measure 30 samples of, i then work out the average, range, and
std deviation, for that point. i then use these figures to work out a
Pp value and/or Ppk value. the difference is that if my spec only has a
lower limit I calculate Pp (ie dia must be greater than X) and if I have
a upper and lower limit I calculate Pp and Ppk (ie dia is between X &
Y), and then using conditional formatting and IF statements show the
results (circle for good, triangle for OK and cross for bad) with both
Pp and Ppk having different criteria

Now it gets complicated!! I could do this easily with two sheets but
TS16949 says that I must use the same sheet for both situations so I
need my sheet to do several things

1. to detect when I am using a lower spec only or an upper and lower
spec and then to select the appropriate equation/set of IF statements
to give the correct result
2. if I am using just lower spec to hide the Ppk Value (grey out/hide
etc..)
AND
3. select the correct Good/OK/Bad criteria.

if anyone thinks they can help PM me and I can show you where I'm upto
on my spreadsheet and maybe it will help you understand.

Thanks in advance for your help
TTFN
Matt


--
matthewrpenny
------------------------------------------------------------------------
matthewrpenny's Profile: http://www.excelforum.com/member.php...o&userid=30826
View this thread: http://www.excelforum.com/showthread...hreadid=504909

Sounds like your doing my kinda work - CMM
NOMINAL 1
UPPER 0.5
LOWER -0.5
ABS ABS(B3)
LSL SUM(NOMINAL-ABS)
USL SUM(NOMINAL+upper)
MIN MIN(DataRange)
MAX MAX(DataRange)
RANGE SUM(MAX-MIN)
AVE AVERAGE(DataRange)
MEDIAN MEDIAN(DataRange)
AVE-NOM SUM(AVE-NOMINAL)
Stdev STDEV(DataRange)
CPK IF(USL-AVEAVE-LSL,(AVE-LSL)/(Stdev*3),(LSL-AVE)/(Stdev*3))
CP SUM(Total/Stdev)
total SUM(upper+ABS)
std by six SUM(Stdev*6)
Ppk (sample size) IF(PpkuPpkl,Ppkl,Ppku)
StdevP STDEVP(DataRange)
SKEW SKEW(DataRange)
Ppku (USL-AVE)/(Stdev*3)
KURT KURT(DataRange)
Ppkl (AVE-LSL)/(3*Stdev)
Ppk (population) IF(USL-AVEAVE-LSL,(AVE-LSL)/(stdevP*3),(LSL-AVE)/(stdevP*3))

Ppk for the entire population uses same formula as
Cpk, €¦€¦...except instead of
Stdev ,
StdevP
is used!
I have spreadsheets ready for dimensional layout, If you need a copy of some
let me know

I will forward you some great samples if you wish



"matthewrpenny" wrote:


Hi All

I need a bit of help with a (I think) complicated excel problem.

I am using excel to help keep track of process capability studies
(Checking the capability of a manufacturing process for those that
don't know)

What I have is a drawing dimmension (in this case a diameter) that I
have to measure 30 samples of, i then work out the average, range, and
std deviation, for that point. i then use these figures to work out a
Pp value and/or Ppk value. the difference is that if my spec only has a
lower limit I calculate Pp (ie dia must be greater than X) and if I have
a upper and lower limit I calculate Pp and Ppk (ie dia is between X &
Y), and then using conditional formatting and IF statements show the
results (circle for good, triangle for OK and cross for bad) with both
Pp and Ppk having different criteria

Now it gets complicated!! I could do this easily with two sheets but
TS16949 says that I must use the same sheet for both situations so I
need my sheet to do several things

1. to detect when I am using a lower spec only or an upper and lower
spec and then to select the appropriate equation/set of IF statements
to give the correct result
2. if I am using just lower spec to hide the Ppk Value (grey out/hide
etc..)
AND
3. select the correct Good/OK/Bad criteria.

if anyone thinks they can help PM me and I can show you where I'm upto
on my spreadsheet and maybe it will help you understand.

Thanks in advance for your help
TTFN
Matt


--
matthewrpenny
------------------------------------------------------------------------
matthewrpenny's Profile: http://www.excelforum.com/member.php...o&userid=30826
View this thread: http://www.excelforum.com/showthread...hreadid=504909