The Bradford Factor in Excel
The Bradford Factor is the number of spells of absence, squared, times the total days absent: S × S × D. In Excel, =B2^2*C2 with spells in B2 and days in C2. It scores lots of short absences higher than one long one.
A worked example
Over a year, Ava had one absence of 10 days. Ben had 5 separate one-day absences. Both lost some time, but the factor treats them very differently:
- Ava: 1 spell, 10 days. 1 × 1 × 10 = 10.
- Ben: 5 spells, 5 days. 5 × 5 × 5 = 125.
=B2^2*C2
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Spells | Days | Bradford Factor |
| 2 | Ava | 1 | 10 | 10 |
| 3 | Ben | 5 | 5 | 125 |
| 4 | Cara | 3 | 6 | 54 |
Straight from an absence log
Usually you have a log with one row per spell of absence, not a summary. COUNTIF counts each person's spells and SUMIF adds up their days (see SUMIFS):
- Spells:
=COUNTIF($A$2:$A$9,F2) - Days:
=SUMIF($A$2:$A$9,F2,$C$2:$C$9) - All in one:
=COUNTIF($A$2:$A$9,F2)^2*SUMIF($A$2:$A$9,F2,$C$2:$C$9)
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Name | First day | Days | Name | Spells | Days | Factor | ||
| 2 | Ben | 12/01/2026 | 1 | Ava | 1 | 10 | 10 | ||
| 3 | Ava | 02/02/2026 | 10 | Ben | 5 | 5 | 125 | ||
| 4 | Ben | 16/03/2026 | 1 | ||||||
| 5 | Ben | 04/05/2026 | 1 | ||||||
| 6 | Cara | 11/05/2026 | 2 | ||||||
| 7 | Ben | 22/06/2026 | 1 | ||||||
| 8 | Ben | 14/09/2026 | 1 |
To get the spells from first and last dates, count working days for each row first with NETWORKDAYS.
Using it fairly
Employers set their own trigger points, such as a conversation at 50 and a formal review at 200. The score is a prompt for a conversation, not a decision: absences linked to disability, pregnancy or a long-term condition need different treatment. Check your organisation's policy.
Common mistakes
Squaring the days instead of the spells
=B2*C2^2 squares the days, so Ava's single 10-day absence scores 100 instead of 10. It's the spells that are squared.
Counting a long absence as several spells
One absence that runs over a weekend is still one spell. Log each spell once, from first day off to last.
Unlocked ranges in the summary
Without $ signs, COUNTIF's range slides down as you copy, and later people lose spells. Lock the log ranges.
Try it now
In D2, work out each person's Bradford Factor: their number of spells of absence, squared, times their total days absent. Your formula will be copied down to D7.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Spells | Days | Bradford Factor |
| 2 | Ava | 1 | 10 | |
| 3 | Ben | 5 | 5 | |
| 4 | Cara | 3 | 6 | |
| 5 | Dev | 2 | 14 | |
| 6 | Eve | 4 | 4 | |
| 7 | Femi | 0 | 0 |