Weighted Averages Are Lying to You: Outliers, Seasonality, and the Limits of the Master Spreadsheet

Somewhere in your firm there is a master spreadsheet. It holds every unit price anyone thought to save, it is maintained by one conscientious person (and forked by five less conscientious ones), and its weighted averages price most of the estimates that go out the door. This article is about the specific, predictable ways that spreadsheet misleads you — even when every number in it is accurate.

The average is not the market

A weighted average answers one question: what did this item cost, on average, across the projects in my data? An estimate needs a different question answered: what will the low bidder price this item at, on this project, in this place, in this season? The gap between those two questions is where estimates go wrong — and no amount of spreadsheet hygiene closes it, because the problem is structural. Five structural failures, specifically:

1.Outliers skew even weighted data

Every estimator who works with bid exports learns this within a month: the weighted averages can’t be trusted raw. One unbalanced bid — a pennied-out item, a mobilization stuffed with front-loaded cash — drags the average far from anything the market actually charges. So you cull the outliers by hand and re-weight. Which works, until a client asks how you decided which data points didn’t count. Hand-culling isn’t wrong; it’s undocumented judgment — and undocumented judgment is exactly what an estimate review attacks.

2.The calendar isn’t in the spreadsheet

The same project bid in January and August can price 10–20% apart — freeze-thaw windows, backlog cycles, the seasonal rhythm of when work is designed versus when it’s bid. A spreadsheet that averages a February award with two July awards produces a number that was true on no date at all. Unless the letting month is a variable in your pricing — and in almost every firm’s spreadsheet, it isn’t — every average carries a hidden seasonal error.

3.Geography is destiny, and averages erase it

The identical scope prices differently in Bergen County than in Passaic County — different haul distances, different plants, different bidder pools, different competitive pressure. Averaging across counties blends genuinely different markets into a number that describes none of them. The error compounds when a firm’s history is concentrated where it happens to have worked, then applied where it happens to be estimating.

4.Quantity and project size bend unit prices

A hundred tons of asphalt on a $1M municipal job and a hundred tons on a $100M program are different products with different prices. Volume discounts, mobilization amortization, and the seriousness of the bidder pool all move with project scale. A flat average treats a unit price as a property of the material. It’s a property of the material in a context.

5.The matching problem nobody talks about

Before you can average an item’s history, you have to decide which rows are the same item. At the DOT level, item numbers help. Below it, the naming is the Wild West — the same 15-inch pipe appears under dozens of wordings across municipalities. Across the public record as a whole, tens of millions of uniquely-worded line items collapse into only about twenty thousand things that actually get priced. A firm’s spreadsheet handles this with text-matching and memory — which means every average silently includes rows that don’t belong and misses rows that do.

Every failure above is invisible in the spreadsheet itself. The numbers look clean. The math checks. The answer is still wrong.

What “better” actually requires

None of this argues for abandoning historical data — it argues for demanding more of it:

• Outlier handling that’s systematic, not personal. The same statistical treatment applied to every item, every time — documentable in one sentence when a client asks.
• Season, geography, and scale as model inputs, not afterthoughts. The question isn’t “what has this item averaged?” but “what does this item do in this county, in this month, at this quantity, on a project this size?”
• A standardized materials catalog so the history you’re drawing on is actually the same item, whatever a municipality happened to call it.
• Distributions instead of single numbers. The shape of an item’s pricing history — the sweet spot, the spread, the tails — carries more information than any average, and it’s the exhibit that survives review.

Where PinPoint fits

The spreadsheet was never the problem — it was the best available answer to a data-poor world. That world ended. Keep the spreadsheet for what it’s good at; stop asking it to predict a market it was never built to see.