Statistical FunctionsIntermediate

Flag an Unusual Week of Water Use with AVERAGE and STDEV

Every week's meter reading is different, so "higher than average" flags half of them. What a facilities manager needs to know is whether a week is further from normal than the usual week-to-week wobble can explain, and the standard deviation is that wobble measured. Two of them from the average is the usual line between noise and something worth walking the pipes for.

Task:

You look after facilities for a 60-room hotel, and the water company bills by the cubic metre. The last ten weekly meter readings are in A2:B11. In C2, show "Check" if that week's use is more than two standard deviations away from the ten-week average, in either direction, and "OK" otherwise. Work the average and the standard deviation out of B2:B11 inside the formula, then copy it down to C11.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

11 rows × 3 columns10 cells you fill in
ABC
1Week commencingWater used (m³)Status
29/7/2026412
39/14/2026398
49/21/2026405
59/28/2026421
610/5/2026389
710/12/2026476
810/19/2026402
910/26/2026415
1011/2/2026393
1111/9/2026612
What this exercise teachesMay contain the answer

A threshold such as "more than 450 m³" has to be guessed, and it is wrong the moment occupancy changes. Measuring the spread of the data itself avoids the guess. AVERAGE($B$2:$B$11) is a normal week, 432.3 m³. STDEV($B$2:$B$11), about 67.7 m³, is how far a typical week sits from that average, the wobble you get from guests, laundry and the weather. Dividing one week's gap by it says how many of those typical wobbles away the week is, and two is the usual line: for data that wobbles at random, only about one reading in twenty strays that far, so passing it is rarely chance. The week of 11/9 used 612 m³, 179.7 above the average, which is 2.65 standard deviations, and is flagged. The week of 10/12 is the instructive one: 476 m³ looks high beside its neighbours, but it is only 0.65 standard deviations out, and a conference that week would explain it without anyone lifting a floor tile. ABS is there because the test has to work in both directions; a sudden drop can mean a stuck meter or an estimated reading, and without ABS a large negative gap would never pass "> 2 × STDEV". One limit is worth knowing: the leak week is part of the ten readings, so it pulls the average up and widens the standard deviation itself, which makes a single big outlier harder to catch. With only ten weeks it still clears the line here; with a shorter history, compare each week against the weeks before it instead.