01 / 11The Chart Said Twenty Days In Control

StatisticsData QualityBlank Cells

Control Chart Limits in Excel With AVERAGE and STDEV.S

The Control Chart Kept Line 2 In Control for Twenty Days, Including the Day It Packed 41,600 Underweight Bags, Because Two Down Days Were Typed as Zero

2 Nov 20268 min read

UsesSTDEV, VARAVERAGEFILTERCOUNTIFS

Harrowgate Mills' control chart said Line 2 stayed inside its limits all October. On 23 October it averaged 494.6 g in a 500 g bag. Two maintenance days typed as 0 g had stretched the limits until nothing could fall outside them.

Line 2, October 2026Figure
Production days on the sheet20
Down days typed as 0 g2
Mean fill on 23 October494.6 g
z-score the chart gave it+0.29
z-score with down days left out−3.77
Bags rejected at the retailer41,600

Scope. AVERAGE, STDEV.S, STDEV.P, AVERAGEIFS, COUNTIFS and ABS work in every version of Excel since 2010, on Windows, Mac and the web. FILTER needs Microsoft 365 or Excel 2021; section 5 gives an IF version for older copies.

Throughout, the sheet is in A1:F25: date in A, Running or Down in B, the day's mean fill in grams in C. Rows 2 to 21 are the twenty days. Rows 22 to 25 hold the mean, standard deviation and limits.

01The Chart Said Twenty Days In Control

Each day the quality lead typed Line 2's mean fill weight into column C. On the two maintenance days the line packed nothing, so the cell got a 0. Column D scored every day against the month's own mean and standard deviation:

=(C2-AVERAGE($C$2:$C$21))/STDEV.S($C$2:$C$21)

A day more than three standard deviations from the mean is out of control. No cell in column D passed 3 or −3, so the chart showed a quiet month.

Twenty Days, Two Zeros, No Day Out of Control

Line 2's October fill-weight sheet at Harrowgate Mills, in A1:F25. Column B says whether the line was Running or Down, and C holds the day's mean fill in grams, with the two down days typed as 0. Column D is the z-score against all twenty days, zeros included; E is the z-score against the running days only, and F flags anything beyond ±3. Rows 22 to 25 hold the mean, STDEV.S and the ±3 limits for each version.

ABCDEF
1
Date
Status
Mean fill (g)
z, zeros in
z, running only
Outside ±3?
2
01 Oct
Running
500.8
0.33
0.34
No
3
02 Oct
Running
501.2
0.33
0.61
No
4
05 Oct
Running
500.4
0.33
0.08
No
5
06 Oct
Running
499.6
0.32
-0.45
No
6
07 Oct
Running
501
0.33
0.47
No
7
08 Oct
Down
0
-2.92
8
09 Oct
Running
500.6
0.33
0.21
No
9
12 Oct
Running
501.4
0.33
0.74
No
10
13 Oct
Running
500.2
0.32
-0.06
No
11
14 Oct
Running
499.8
0.32
-0.32
No
12
15 Oct
Running
500.9
0.33
0.41
No
13
16 Oct
Running
501.1
0.33
0.54
No
14
19 Oct
Running
500.5
0.33
0.14
No
15
20 Oct
Down
0
-2.92
16
21 Oct
Running
499.9
0.32
-0.25
No
17
22 Oct
Running
500.7
0.33
0.28
No
18
23 Oct
Running
494.6
0.29
-3.77
Yes
19
26 Oct
Running
501.3
0.33
0.67
No
20
27 Oct
Running
500.3
0.32
0.01
No
21
28 Oct
Running
500.8
0.33
0.34
No
22
Mean (g)
450.26
500.28
23
STDEV.S (g)
153.99
1.51
24
Lower limit (g)
-11.72
495.76
25
Upper limit (g)
912.23
504.81

fxCells with formulas are highlighted in green

Hover over formula cells to see the formula and highlight referenced cells

On 23 October a filler head drifted and the line averaged 494.6 g all day. Column D gave it +0.29, slightly above average. The retailer's goods-in check weighed the pallets and rejected all 41,600 bags.

02Two Zeros Moved the Mean by 50 Grams

A zero is a measurement. AVERAGE and STDEV.S count a 0 exactly as they count 500.4, so two empty days became two bags that weighed nothing. They dragged the mean down and blew the standard deviation up a hundredfold.

StatisticWith the zerosRunning days only
Mean450.26 g500.28 g
STDEV.S153.99 g1.51 g
Lower limit−11.72 g495.76 g
Upper limit912.23 g504.81 g

A lower limit of −11.72 g is a bag that weighs less than nothing. Any limit you could not physically hit is a sign the inputs are wrong, not that the process is calm.

Scenario: Put the twenty days in A2:C21 with 0 in C7 and C15. Enter =AVERAGE(C2:C21) in D22 and =STDEV.S(C2:C21) in D23, then check D22 reads 450.26 and D23 reads 153.99.

Try it in the grid

03A z-Score Is Distance in Standard Deviations

A z-score says how many standard deviations a value sits from the mean. It only means something if the standard deviation describes the data. Measured against 153.99 g, every real bag looks like the mean and every zero looks mildly low.

DayFillz, zeros inz, running only
22 Oct500.7 g0.330.28
23 Oct494.6 g0.29−3.77
08 Oct0 g−2.92blank

With zeros in, the underweight day sits above the mean of 450.26 g, so its z is positive. Against the running days it is 3.77 standard deviations short.

Excel also has STANDARDIZE, which takes the value, mean and standard deviation: =STANDARDIZE(C18,$E$22,$E$23) returns −3.77. It does the same subtraction and division; it does not fix the inputs.

Scenario: Enter =(C18-AVERAGE($C$2:$C$21))/STDEV.S($C$2:$C$21) for 23 October and read +0.29. Then clear C7 and C15 and watch the same cell drop to −3.77.

Try it in the grid

04Leave a Down Day Blank, Not Zero

The simplest fix is in the data. AVERAGE and STDEV.S skip empty cells and text, so a blank or the word "Down" in column C drops out of both without touching a formula.

The z-score formula then needs one guard, so a blank day stays blank instead of scoring as a zero:

=IF(C2="","",(C2-$E$22)/$E$23)

Watch the cousins. STDEVA and AVERAGEA count text as 0, so "Down" typed into C would bring the problem straight back. Use the plain functions.

Scenario: Clear C7 and C15, enter =AVERAGE(C2:C21) in E22 and =STDEV.S(C2:C21) in E23. Check E22 reads 500.28 and E23 reads 1.51, then type Down in C7 and confirm neither moves.

Try it in the grid

05Or Filter by Status in the Formula

Sometimes the zeros arrive from a system export and you cannot clear them. Let column B decide which days count instead of the values in C:

=AVERAGEIFS(C2:C21,B2:B21,"Running")
=STDEV.S(FILTER(C2:C21,B2:B21="Running"))

There is no STDEVIFS, which is why the second line filters first. Both return the running-day figures, 500.28 g and 1.51 g, with the zeros still in the sheet.

In Excel 2019 or earlier, =STDEV.S(IF(B2:B21="Running",C2:C21)) does the same job. Confirm it with Ctrl+Shift+Enter; STDEV.S ignores the FALSE that IF returns for down days.

Scenario: Leave the zeros in C7 and C15 and enter =STDEV.S(FILTER(C2:C21,B2:B21="Running")) in E23. Check it reads 1.51, then change B7 to Running and watch it jump to about 115.

Try it in the grid

06Set the Limits From a Clean Baseline

Even with the zeros gone, the bad day helps set the limits it is judged against. The 494.6 g day alone stretches the standard deviation from 0.53 g to 1.51 g.

Limits fromMeanSTDEV.SLower limitz for 23 Oct
All 18 running days500.28 g1.51 g495.76 g−3.77
17 days without 23 Oct500.62 g0.53 g499.03 g−11.34

Control charts are meant to work in two stages. Set the limits from a period you trust, then fix them and judge each new day against them. Recalculating over every day lets a run of drift widen its own limits.

Scenario: Copy E22 and E23 and paste them as values into G22 and G23. Point the z-score formula at $G$22 and $G$23 instead, so the limits stay put when November's days arrive.

Try it in the grid

07STDEV.S or STDEV.P

The two differ by one in the divisor. STDEV.S divides by n − 1 and STDEV.P by n, so the sample version is slightly larger and the limits slightly wider.

FunctionRunning daysLower limit
STDEV.S1.51 g495.76 g
STDEV.P1.47 g495.88 g

Eighteen days are a sample of how Line 2 runs, not the whole of it, so use STDEV.S. The gap shrinks as days are added. Neither function rescues a column full of zeros.

08Count the Days Out of Control

A chart can be misread; a count is harder to ignore. COUNTIFS counts both tails and skips the blank down days, which ABS would turn into #VALUE!:

=COUNTIFS(E2:E21,">3")+COUNTIFS(E2:E21,"<-3")

On October's sheet column D returns 0 and column E returns 1. A monthly total of zero on a filling line deserves the same suspicion as a lower limit below zero.

Scenario: Enter =COUNTIFS(E2:E21,">3")+COUNTIFS(E2:E21,"<-3") in F22. Check it reads 1, then point a copy at D2:D21 and see it read 0.

Try it in the grid

09Five Traps

  1. Zero for no reading. A 0 is a measurement, and it drags the mean and stretches the spread.
  2. The A functions. STDEVA and AVERAGEA turn the text "Down" back into 0.
  3. Limits you could never hit. A lower limit below zero grams means the inputs are wrong.
  4. Limits that include the bad days. Drift widens its own limits if every day sets them.
  5. ABS over blanks. ABS("") returns #VALUE!; count the tails with COUNTIFS.

10Four Exercises

  1. Reproduce it. Score all twenty days with the zeros in and confirm nothing passes ±3.
  2. Blank it. Clear the down days and check that 23 October reads −3.77.
  3. Filter it. Put the zeros back and rebuild the limits with AVERAGEIFS and FILTER.
  4. Freeze it. Set the limits from the 17 clean days as values and score the month again.

What to Take Away

Every weight on Harrowgate's sheet was right, and so was the arithmetic. Two cells recorded "nothing happened" as "a bag weighed 0 g", and the statistics believed them.

Leave a missing reading blank, or filter by status, before AVERAGE and STDEV.S see it. Then check that your limits are weights a bag could actually have.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge