Averages Done Right in Excel: Weighted Means, Averages of Rates & Ratios, Geometric Growth, Time-Weighted Balances & the Average-of-Averages Trap

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Cost six deliveries of one product, compute the true weighted average unit cost with SUMPRODUCT ÷ SUM, score a training course with percentage weights and again with weights given as points.
• Compute the conversion rate of six stores, the pooled rate of the whole campaign and the misleading AVERAGE of the store rates, then find the overall gross margin of four product lines from totals.
• Weight five warehouse averages by their order counts to get the true overall delivery time, measure how far the plain average of averages is off, and build team averages from raw ticket rows with AVERAGEIFS.
• Time four legs of a delivery route, find the true average speed with the harmonic mean, repeat it for legs of different length, and work out the average price paid per roll when the same amount is spent every month.
• Compute year-on-year growth of online sales, the average annual growth from the first and last level, the geometric mean of six member-growth rates and how much the plain AVERAGE overstates it.
• Count how many days the operating account stayed at each balance in October, compute the time-weighted average balance, compare it with the plain average and work out a month's interest on it.
• Smooth twelve weeks of orders with a 3-week and a 5-week moving average, build a linearly weighted 3-week average with SUMPRODUCT and measure how far a long window lags behind a rising trend.
• Add cumulative units to a price-point table, find the weighted median price, compute the weighted mean price per unit sold and chart the four averages side by side.