Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts

    Establish a Pattern in Excel

    Hello,
    I am tracking workdays the employee has called in sick in Excel. I need to know if there is a way in Excel to establish if there is a pattern in the workday that the employee is calling in sick.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,469
    Thanks
    30
    Thanked 61 Times in 57 Posts
    This is quite a mathematical science, I suspect.
    I think you could try to approach patterns by graphing the sick days and running a trend line.
    You might also think about associating the sick days with days of the week and doing a calculation to see if there is a trend line for the various days of the week.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    On the other hand, if it's every Friday and Monday off, you probably don't need Excel.

    zeddy

  4. #4
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Thanks for the response. I am attaching a sample worksheet that assigns each workday a number. Mon=1, Tues=2...etc I am trying to find the best way to chart the data showing the most requested sick leave days off. Thanks!
    Attached Files Attached Files

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Mostate

    ..see attached file for fomulas and chart to get you started.

    zeddy
    Attached Files Attached Files

  6. The Following User Says Thank You to zeddy For This Useful Post:

    MOSTATE (2013-11-01)

  7. #6
    2 Star Lounger
    Join Date
    Apr 2009
    Posts
    114
    Thanks
    18
    Thanked 0 Times in 0 Posts
    This is a perfect starting point...thanks so much!

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    if there is a way in Excel to establish if there is a pattern in the workday that the employee is calling in sick.
    MOSTATE,

    As mentioned, would combinations of sick days also constitute a pattern. if so, there are 30 different combinations and they can all be graphed as well. Is there any reason you are not using a validated list for the week days as opposed to a Vlookup? You can use the formula =COUNTIF($C:$C,"Monday") to sum them instead and have no need for the Numeric Value list. Or, perhaps you are importing data with the weekday represented as a number?

    combinations.png

    combinations2.png
    Attached Files Attached Files

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Mostate

    Perhaps you also need to consider the individual employee data compared against the whole group?
    You could have a file with ALL sick days from ALL employees.
    Then you could do things like count total number of sick days by month, to check for seasonal influences (influenzas?).
    You could check overall 'favourite' sick days for all employees etc etc.
    Personally, if anyone has even a hint of sickness I much rather they don't come in and infect me.

    zeddy

Posting Permissions

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