QI Macros Histogram - QI Macros Excel Add-in

QI Macros Histogram: Formulas and Calculations

The purpose of this document is to provide detailed information on the formulas in the QI

Macros. This paper provides the formulas, a sample calculation and a histogram run in

the QI Macros using the same data provided.

Formulas for Cp and Cpk

Cp =

(USL ? LSL )

6¦Ò?

(USL ? X )

CpU =

3¦Ò?

(X ? LSL )

CpL =

3¦Ò?

Cpk = Min(CpU , CpL )

Formulas for Pp and Ppk

For Process Performance, use standard deviation (¦Ò) of the population instead of sigma

estimator:

Pp =

(USL ? LSL )

6¦Ò

(USL ? X )

PpU =

3¦Ò

(X ? LSL )

PpL =

3¦Ò

Ppk = Min(PpU , PpL )

?MMVI KnowWare International Inc.

1

Another View of the Formulas

Formulas for Cp and Pp

Cp

Pp

(

USL-LSL )

(6 * sigma estimator)

(

USL-LSL )

(6 * standard deviation)

Use when you have a sample

Use when you have the total population

Formulas for Cpk and Ppk

Cpk

Ppk

Minimum of CpU and

CpL

Minimum of PpU and

PpL

CpU

PpU

(

USL-Xbar

)

(3 * sigma estimator)

CpL

(

USL-Xbar

)

(3 * standard deviation)

PpL

( Xbar - LSL )

(3 * sigma estimator)

( Xbar - LSL )

(3 * standard deviation)

Use when you have

a sample

Use when you have

the total population

Points to note:

? Xbar = the average of the data points = ¡ÆX /n

? Changing the spec limits, will change Cp and Pp and may change Cpk and Ppk

? Cp and Cpk use sigma estimator because they assume your data represents a

sample of the population

? Pp and Ppk use standard deviation because they assume your data represents the

total population

?MMVI KnowWare International Inc.

2

Formula for Sigma Estimator

Standard deviation of a population can be estimated from the average range or average

standard deviation of the subgroups in each sample. These are used to calculate Cp and

Cpk.

s? =

R

d2

s

s? =

c4

For n=1-4, use R/d2 formula; for n>4 use s/c4 formula

Here is Another Way of Looking at It

Subgroup Size

Sigma Estimator Formula

1 to 4

( Rbar )

( d2 )

5 or more

( Sbar )

( c4 )

Definitions

Rbar = Average of the

ranges

d2 is a constant based on the

sample size

Sbar = Average of the

standard deviations

c4 is a constant based on the

sample size

Constants for Sigma Estimator Calculation

(Source:ASTM Manual on Presentation of Data and Control

Chart Analysis ¨C Table 16, 2002)

Subgroup

Size

1

2

3

4

5

6

7

8

9

10

11

12

13

14

Constant

d2

d2

d2

d2

c4

c4

c4

c4

c4

c4

c4

c4

c4

c4

Value

1.128

1.128

1.693

2.059

0.94

0.9515

0.9594

0.965

0.9693

0.9727

0.9754

0.9776

0.9794

0.981

Subgroup

Size

26

27

28

29

30

31

32

33

34

35

36

37

38

39

Constant

c4

c4

c4

c4

c4

c4

c4

c4

c4

c4

c4

c4

c4

c4

?MMVI KnowWare International Inc.

Value

0.9901

0.9905

0.9908

0.9912

0.9915

0.9917

0.992

0.9922

0.9925

0.9927

0.9929

0.9931

0.9933

0.9935

3

0.9823

0.9835

0.9845

0.9854

0.9862

0.9869

0.9876

0.9882

0.9887

0.9892

0.9896

c4

c4

c4

c4

c4

c4

c4

c4

c4

c4

c4

15

16

17

18

19

20

21

22

23

24

25

40

41

42

43

44

45

46

47

48

49

50

c4

c4

c4

c4

c4

c4

c4

c4

c4

c4

c4

0.9936

0.9938

0.9939

0.9941

0.9942

0.9944

0.9945

0.9946

0.9947

0.9948

0.9949

Formulas for One Sided Spec Limits

LSL Only

Cp = Cpk = CpL

Pp = Ppk = PpL

USL Only

Cp = Cpk = CpU

Pp = Ppk = PpU

Formula for Defects in Parts Per Million

Actual

Estimated for Population

(# of non conforming)*1000000

(# of parts)

PPMU = NORMSDIST(Z upper)*1000000

+

PPML = NORMSDIST(Z lower)*1000000

Formulas for Z Scores

Z scores help estimate the non-conforming PPM. Z scores standardize +/-3* sigma

estimator values into +/-3.

Zlower

Zupper

Zbench is the Z score for the

Expected PPM

ZT (target) = Cpk for a target

value instead of the USL or

LSL. If not defined, use the

midpoint between the USL and

LSL

(LSL-Xbar)/sigest

(USL-Xbar)/ sigest

normsinv(1-(Expected PPM/1,000,000))

(Xbar-Target)/(3*sigest)

?MMVI KnowWare International Inc.

4

Sample Calculation

Let's perform calculations using the following sample data from Montgomery, Intro to

SPC, 4th Ed., pgs. 353-358. You can download this data as part of the QI Macros test

data at Open the

spreadsheet and click on the histogram tab.

Sample

S1

S2

S3

S4

S5

S6

S7

S8

S9

S10

S11

S12

S13

S14

S15

S16

S17

S18

S19

S20

?

?

?

?

?

Obs

1

265

268

197

267

346

300

280

250

265

260

200

276

221

334

265

280

261

250

278

257

Obs

2

205

260

286

281

317

208

242

299

254

308

235

264

176

280

262

274

248

278

250

210

Obs

3

263

234

274

265

242

187

260

258

281

265

246

269

258

265

271

253

260

254

265

280

Obs

4

307

299

243

214

258

264

321

267

294

283

328

235

263

272

245

287

274

274

270

269

Obs

5

220

215

231

318

276

271

228

293

223

277

296

290

231

283

301

258

337

275

298

251

Assume the USL = 346 and the LSL = 200.

Since there are 5 subgroups, sigma estimator will use the formula SBar/c4

If we look in the table above, the constant for a subgroup of 5 is 0.94

Other calculations for this data set are:

o Xbar = 26,446/100=264.46

o Standard deviation = 31.85

o Sigma estimator = ( SBar/c4 ) = (30.02/.94) = 31.93

If you are trying to recalculate this manually, use the statistical functions in Excel to

calculate: standard deviation, normdist and normsinv.

?MMVI KnowWare International Inc.

5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download