+ Reply to Thread
Results 1 to 12 of 12

Bradford Factor - How to count number of instances while using words

  1. #1
    Registered User
    Join Date
    05-25-2012
    Location
    toronto
    MS-Off Ver
    Excel 2010
    Posts
    31

    Lightbulb Bradford Factor - How to count number of instances while using words

    Hi,

    I need to figure out a way to count the number of times a person has taken leaves and count the number of instances.

    Bradford example.xlsx

    Please do take a look at the file and tell me is there is a way in which this can be solved.
    Thanks

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Bradford Factor - How to count number of instances while using words

    There WILL be a nice way to do this. In the meantime, here's an ugly way...

    You will run into a couple of other issues - what happens at month ends - if sick leave runs over the end of one month and the start of another. You might need to think a bit more about the basic structure of your data to get round this.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-25-2012
    Location
    toronto
    MS-Off Ver
    Excel 2010
    Posts
    31

    Re: Bradford Factor - How to count number of instances while using words

    Neat trick Glenn.... Thanks for your prompt response...
    I sure didn't think this through... Wow month end... I initially assumed that the employees are going to be constant but well as it turns out, we do hire a lot of temps and the employee list changes every month. Another crude method would possibly be to use a vlookup for an entire years worth of attendance from individual excel files for each month[That's how the data was collected last year - one excel file per month]. Any ideas on how one could possibly collate the data in an easier way?
    I wish I had collected the data as line items.
    Last edited by vgr; 01-03-2014 at 02:29 PM.

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Bradford Factor - How to count number of instances while using words

    there is no NICE way to calculate or use the bradford factor
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Registered User
    Join Date
    05-25-2012
    Location
    toronto
    MS-Off Ver
    Excel 2010
    Posts
    31

    Re: Bradford Factor - How to count number of instances while using words

    Quote Originally Posted by martindwilson View Post
    there is no NICE way to calculate or use the bradford factor
    No nice way to use the factor? why so? The logic seems sound. Short frequent leaves do a lot more than leaves taken at a stretch in a production setting.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Bradford Factor - How to count number of instances while using words

    Martin,

    You're right... It's a bit of a "blunt instrument" that, in my experience, tends to cause distress to those with genuine problems.

  7. #7
    Registered User
    Join Date
    05-25-2012
    Location
    toronto
    MS-Off Ver
    Excel 2010
    Posts
    31

    Re: Bradford Factor - How to count number of instances while using words

    Quote Originally Posted by Glenn Kennedy View Post
    Martin,

    You're right... It's a bit of a "blunt instrument" that, in my experience, tends to cause distress to those with genuine problems.
    @Martin, Glenn:
    Is there a better alternative to the Bradford Factor?
    It's just that absenteeism is really tough to control. The disruptions it causes lead to major changes in the planning and scheduling......

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Bradford Factor - How to count number of instances while using words

    If I can figure out how... I'll send you a PM.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Bradford Factor - How to count number of instances while using words

    Try this - it runs over a year & takes both weekends and holidays into account. I'm offline now (pub time...). Back tomorrow.
    Attached Files Attached Files

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,821

    Re: Bradford Factor - How to count number of instances while using words

    There's nothing wrong with the Bradford Factor if it is used as a measurement tool and not as a blunt instrument to hit staff and managers with. It highlights patterns of absenteeism which can then be discussed with the member of staff. It may be they're not very good at getting up to come to work on a Monday after a weekend on the beer but, equally, there could be some underlying health issue. Hopefully, if it is the latter, both the manager and HR would be aware of the issue and it would be taken into account.

    Unfortunately, in my experience, it does become a blunt instrument ... a measure, which becomes a target, which must then be reduced. Otherwise, it then reflects on the performance and bonus of the individual and all the managers in the management chain. And, as consequence, it goes the way of all measures, targets and statistics ... a way of working around it must be found ... usually termed cheating. OR, a performance improvement plan is put in place to manage the member of staff's absenteeism, putting both the individual and the manager under pressure and causing stress.

    So, all that said, I have to agree with Martin. There's no NICE way of measuring the Bradford Factor. It WILL turn nasty.

    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  11. #11
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Bradford Factor - How to count number of instances while using words

    .................................................................deleted
    Last edited by martindwilson; 01-04-2014 at 08:48 AM.

  12. #12
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    604

    Re: Bradford Factor - How to count number of instances while using words

    This should be done via programming. This is an AHK script that does it, however the logic will be identical to VBA.

    HTML Code: 
    !h::
    XL := Excel_Get()
    num_days := 31
    
    Loop, %num_days%
    	varTot := varTot XL.Cells(3, A_Index+2).Value		;concatenating all cells from C3 to AG3
    
    Loop, %num_days%
    	term := term "LOP"			;creates a long "LOPLOPLOPLOPLOP.." string
    
    Loop, %num_days%
    {
    	Loop,
    	{
    		varC := InStr(varTot, term,0,1,1)	
    		if(varC > 1)			;checks if long "LOPLOPLOPLOP...etc" string exists in concatenation
    		{
    			StringReplace, varTot, varTot, %term%, +++
    			counter++			;if it exists, ADD an instance
    		}
    		else
    			break
    	}	
    StringTrimRight, term, term, 3		;trim the string down by one "LOP"
    }
    
    MsgBox, Number of Instances:   %counter%	;total number of instances (eg. "3" in your example)
    return

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Bradford Factor
    By ryho33 in forum Excel General
    Replies: 25
    Last Post: 06-26-2019, 04:34 AM
  2. Bradford factor work sheet
    By butlej6 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-11-2017, 06:02 AM
  3. Bradford Factor
    By francesjan48 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-03-2012, 09:02 AM
  4. requests Bradford Factor
    By martindwilson in forum The Water Cooler
    Replies: 11
    Last Post: 11-04-2010, 07:53 PM
  5. bradford factor
    By Tjenkins in forum Excel General
    Replies: 8
    Last Post: 10-29-2010, 06:38 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1