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,509
    Thanks
    34
    Thanked 66 Times in 62 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
    3,121
    Thanks
    149
    Thanked 569 Times in 542 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
    3,121
    Thanks
    149
    Thanked 569 Times in 542 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,751
    Thanks
    129
    Thanked 690 Times in 626 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
    3,121
    Thanks
    149
    Thanked 569 Times in 542 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
  •