Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
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
1  
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
add comment

1 Answer

up vote 0 down vote accepted

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:

Brandford2

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.

share|improve this answer
    
(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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.