# How to calculate Bradford Factors with Excel 2007?

Bradford Factors are used by some to measure the significance of absenteeism and are computed for each individual as: S squared times D, where S is the number of spells (continuous periods of absence) and D is the sum of the days. The calculation is often made over a rolling 52 weeks.

Commercial HR software often has the facility to calculate these factors but a Google search indicates quite a lot of interest without any free solutions.

Using units of half a day and including any non-working days in each spell, how does one calculate the factors using Excel 2007?

-
So I need to bunch up my sick days if I want to fool HR! five spells of 5 day would score 5✕5✕5=125 where as one spell of 5 days is just 1✕1✕5=5. I could be better taking the whole year off. – richard Sep 19 '12 at 21:33
@richard There are probably as many people who think it is all nonsense as think it valuable. – pnuts Sep 19 '12 at 23:22
I suppose that the important work is significance. But significant of what? I guess you investigate the significant cases to see what the problem is: fraud, bad health, an unlucky year, etc. (I don't judge it as nonsense, I am just a sceptic (according to the true meaning of the word, neither believing or disbelieving). – richard Sep 20 '12 at 8:02
@richard According to LeavePlanner.com the UK Prison Service placed what looked like excessive faith in them (51 points – verbal warning; 201 points – written warning; 401 points – final warning; 601 points – dismissal) but they do have a very big problem with absenteeism – and it seems the courts have forced them to be less rigid (see Q6 – pnuts Sep 20 '12 at 10:23

Using a table that contains data labels `A1:F1` of: Number (Staff identifier), Forename, Surname, From (start date of absence), To (end date of absence, not inclusive) and Spells (an indicator, where A = Paid, Full day, AH = Paid, Half day, AU = Unpaid, Full day and AHU = Unpaid, Half day):

1. Add `G1` ‘Days’, `G2` `=IF(ISERROR(SEARCH("H",F2)),E2-D2,(E2-D2)/2)` and copy down.
2. Create pivot table in `I1` from Columns `A:G` with `Number`, `Forename`, `Surname` as Row Labels, `Count of Spells` and `Sum of Days` as Data Fields (Sigma Values),and `*Sigma* Values` as Column Labels. Format `Sum of Days` as `Fraction`, `Up to one digit (1/4)`.
3. If ticked, uncheck `Totals&Filters`, `Grand Totals`, `Show grand totals for rows`, `Show grand totals for columns` and `Display`, `Show expand/collapse buttons`.
4. Click on `Show in Tabular Form`.
5. Sort `Number` ascending.
6. Add `N2` ‘BF’, `N3` `=L3^2*M3` and copy down.

Dummy data (`From` in date order ascending) and resultant pivot table are shown below:

To confine coverage to say 52 weeks, Change Data Source based on dates in `ColumnD`.

Note this may require manual adjustment to cope with absences that began before the start of the data source range.

-
(1) Your data look sketchy. How/why was Colin Masters absent 10 times, and they're all the same date? (2) What does your answer do if a person is off half of Tuesday, and then all day Wednesday and Thursday? I'm not going to test it myself, because I don't know how to create a PivotTable, but it looks like your solution would count that as two absences: one of 0.5 days and one of 2 days. But really, the correct answer is that it is one absence of 2.5 days. That's what this other question is all about. – Scott Sep 19 '12 at 23:15
Also, you got the caption of your image wrong! – Scott Sep 19 '12 at 23:21
@Scott 'Colin Masters' should indeed be ten different days - but 'he' and a few others were there to check the results against those on the wiki page. 'Half Tuesday' might indeed be a problem but the dummy data does not capture whether the first half or the second half day for spell, so the above does indeed default to two spells. The caption 'error' was deliberate (though I never expected anyone to notice and am pleased you did!). Creating a PivotTable is easy and the results so powerful I would recommend anyone with more than an exceedingly trivial number crunching requirement to 'have a go' – pnuts Sep 19 '12 at 23:34