Bradford Factor

Based on the sample file it seems you're looking for:

Please Login or Register  to view this content.

I am no expert on BF but in the posts I've seen previously the implication was always that consecutive days off were treated as a single absence - it's not clear from your set up if you can capture that or not.
(obviously an ee should not be judged on a single year)

The "official" definition of the Bradford Factor is total number of days off x (total incidents of days off)^2

So any day in work will create a new incident!

So consecutive days off will count as one incident!

note that the Bradford Factor doenst take into account valid reasons for days off or disability discrimination!

Reminds me of the guy who got fired on a sickie, during the 80's, his mistake he was on the front row at the world snooker championships! Ok, why was his boss watching the snooker unless he caught the highlight show in the evening!!!!!!

Firstly can I suggest that you take out the month dividers, its a lot easier to write a formula without the gaps, putting the month to the left of the data and deleting the gaps is a better way to lay out the spreadsheet and more efficient!

Assuming you are ok with the layout, formula I added the formulas to the sheet!

also as a bonus :-

=B77+1+2*(MOD(B77,7)=6)

copied down will continue the weekday series automatically (I placed this in cell b78)

FWIW, I would strongly advised against using SUMPRODUCT with ranges of that magnitude.
Use a limited range and/or if requirements are such that the range referenced must be dynamic use INDEX based DNRs (I would avoid use of OFFSET).

Given OP is using XL2007 the use of SUMPRODUCT is perhaps not necessary ... a COUNTIFS would suffice and would permit much large range references without performance concern.

the file provided was in 2003 format!

@squiggler47, true - I confess I looked at the profile.

Irrespective of version I hope you're not going to defend SUMPRODUCTs referencing 65k rows

Slightly more convoluted but :-


=SUMPRODUCT((D$14:INDEX(D$14:D$65535,COUNTA($B$14:$B$65535),1)="Sick")*((D$15:INDEX(D$15:D$65535,COUNTA($B$14:$B$65535),1))<>"Sick"))

or in 2007+

=COUNTIFS(D14:D5000,"Sick",D15:D5001,"<>Sick")

both perform at around the same speed

Given the set up you can replace the COUNTA with MATCH(9.99E+307,$B:$B)

Using the MATCH you would also (technically) adjust the second MATCH result by +1 row to keep the dimensions equal.

match is actually slowing the calculation by 10%

in Excel 2007

=COUNTIFS(D$14:INDEX(D$14:D$65535,COUNTA($B$14:$B$65535),1),"Sick",D$15:INDEX(D$15:D$65535,COUNTA($B$14:$B$65535),1),"<>Sick")

the original formula ran at about 38 seconds for 1000 calculations, against the sumproduct Calc with index and counta at 0.1 seconds, sumproduct index/match 0.11 and the above at 0.08 seconds and 1.47 second for straight count ifs


Take your pick!!!!

Originally Posted by squiggler47

match is actually slowing the calculation by 10%

I can only say that our results differ.

COUNTA will fire against the used range intersect - as that increases in size so I would expect the binary search driven MATCH to outperform it.

With 30k dates I show that the COUNTA method is approx 33% quicker than original approach and the MATCH is about 20% quicker than the COUNTA.

Whether the difference in real terms would be noticeable is entirely different question

I would say given the small difference that either would be fine, i think 1000 loops in under a second is a reasonable speed since it allows 20 years tracking of 1000 employees before it gets too slow!!!

@Donkey, If the data is unsorted match would either have to index the data to perform a binary search, since it only works with ordered data!

Whereas the last row and column in a worksheet is stored, so I would assume that count limits its self to the last row in counting sort of like A1:min(A1000,LastRow)

Since Match would have to compare each value in an unsorted list and count would only have to verify it had data, I would assume count had advantages with an unsorted list?

Originally Posted by squiggler47

@Donkey, If the data is unsorted match would either have to index the data to perform a binary search, since it only works with ordered data

MATCH when used in Binary Search form assumes data is sorted irrespective of reality.

As to whether MATCH "works" or not rather depends on what you're trying to do.

Here we are interested only in determining row number of last numeric value nothing more - the ordering of the data itself is of no consequence.

Given the facts that:

a) MATCH in this context will return row number of last value <= criteria

b) our "big number" criteria exceeds all values in the precedent range

c) Excel assumes the list is in order

It follows that MATCH will return the row number of the last numeric in the range given it assumes it to be the biggest and it is <= criteria.

@donkey

Final note, in 2010 match is faster, in 2003 its considerably slower, on less than 10k items in a column! Perhaps thats where our discreprency has come in!

Originally Posted by squiggler47

@donkey

Final note, in 2010 match is faster, in 2003 its considerably slower, on less than 10k items in a column! Perhaps thats where our discreprency has come in!

I concur - I just tested on 2002 and Binary Search MATCH is (significantly) slower % wise when compared to COUNTA ... that's a surprise to me.
Might be worthy of further investigation....

@grzegmi Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread