20 Excel formulas every workforce planner should master
Beyond SUM and a plain average. These are the functions that do the real work in a planning workbook — weighted metrics, conditional roll-ups, robust lookups, rounding that matches a roster, the date maths behind capacity, and the statistics behind a forecast. Each links to its own page with the syntax, a worked contact-centre example, a diagram, and the trap to avoid.
Weighted averages and multi-condition sums in one cell.
02 SUMIFSSum with one or many conditions.
03 COUNTIFSCount rows meeting conditions.
04 AVERAGEIFSAverage with conditions.
05 INDEX + MATCHThe robust, two-way lookup.
06 XLOOKUPThe modern lookup that replaces VLOOKUP.
07 VLOOKUPThe classic lookup — used well.
08 IFERRORCatch errors before they spread.
09 IFSClean multi-way logic.
10 ROUNDUP & the rounding familyRound staffing the way operations actually works.
11 TEXTFormat numbers into report-ready labels.
12 WEEKDAYPull the day-of-week pattern out of dates.
13 NETWORKDAYS.INTLCount true working days.
14 EOMONTHJump to month-ends for roll-ups.
15 FORECAST.LINEAR & TRENDProject a trend without a chart.
16 PERCENTILE.INCRead the distribution, not just the average.
17 STDEV.SMeasure variability.
18 CORRELQuantify what drives volume.
19 POISSON.DISTThe maths under Erlang.
20 LETWrite complex formulas you can actually read.
Tip: build these into a personal "planner toolkit" tab so they’re always one copy-paste away.