statistical data, stdev, Pp& Ppk

[matthewrpenny]

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 studie
(Checking the capability of a manufacturing process for those tha
don't know)

What I have is a drawing dimmension (in this case a diameter) that
have to measure 30 samples of, i then work out the average, range, an
std deviation, for that point. i then use these figures to work out
Pp value and/or Ppk value. the difference is that if my spec only has
lower limit I calculate Pp (ie dia must be greater than X) and if I hav
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 th
results (circle for good, triangle for OK and cross for bad) with bot
Pp and Ppk having different criteria

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

1. to detect when I am using a lower spec only or an upper and lowe
spec and then to select the appropriate equation/set of IF statement
to give the correct result
2. if I am using just lower spec to hide the Ppk Value (grey out/hid
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 upt
on my spreadsheet and maybe it will help you understand.

Thanks in advance for your help
TTFN
Mat

 

[ufo_pilot]

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-AVE>AVE-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(Ppku>Ppkl,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-AVE>AVE-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
(e-mail address removed)
I will forward you some great samples if you wish