+ Reply to Thread
Results 1 to 14 of 14

Lab Productivity

  1. #1
    Forum Contributor
    Join Date
    08-24-2011
    Location
    Richmond, Va
    MS-Off Ver
    Excel 2003
    Posts
    130

    Lab Productivity

    Here is some data I need help with (spreadsheet attached)

    I have lab technicians working 24/7 and I am trying to see how best to display “lab technician Efficiency” in excel…….(Who is the most efficient of them all). I am looking for the creative mind


    THANKS in advance
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    Re: Lab Productivity

    Omer:
    Place the technician's initials next to the number of samples analyzed (adding columns between shifts after row 31).
    Create a list of technicians separate from your data fields.

    Doing a countifs next to each name on your list of techies will give you the number of shifts the tech. worked.
    Now do a sumif which will result in the number of samples analyzed by that technician.

    Now divide the number of samples per tech by the number of shifts. Or, use averageif by tech by shift. Sort the table if you like by efficiency.

    Simple.
    Pete

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Lab Productivity

    That assumes that there is always backlog to process, and that techs are not penalized for their having no work to do.
    Last edited by shg; 12-22-2015 at 04:26 PM.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Valued Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    502

    Re: Lab Productivity

    Yes, SHG, But......then what about the 'goody, goody, two shoes' who is always sucking up to Omer and grabs more than everyone else :-)

    I think you would possibly need a 'Difficulty' rating assigned to each sample analyzed then use countif unless of course all of the analysis procedures are exactly the same and take the same amount of time.

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Lab Productivity

    @BlindAlley
    You mean you want to penalize the employee who goes to work to actually work in favour of those who show up to do the minimum possible

    I re-arranged the data to be 1 record per row and one field per column combining both of your charts.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  6. #6
    Forum Contributor
    Join Date
    08-24-2011
    Location
    Richmond, Va
    MS-Off Ver
    Excel 2003
    Posts
    130

    Re: Lab Productivity

    @ PeteABC Thx for the suggestion
    Last edited by omer123456; 12-23-2015 at 11:00 AM.

  7. #7
    Forum Contributor
    Join Date
    08-24-2011
    Location
    Richmond, Va
    MS-Off Ver
    Excel 2003
    Posts
    130

    Re: Lab Productivity

    @ SHG Kinda true :-)
    Last edited by omer123456; 12-23-2015 at 10:57 AM.

  8. #8
    Forum Contributor
    Join Date
    08-24-2011
    Location
    Richmond, Va
    MS-Off Ver
    Excel 2003
    Posts
    130

    Re: Lab Productivity

    @ new doverman Thx for the spreadsheet
    Last edited by omer123456; 12-23-2015 at 10:57 AM.

  9. #9
    Forum Contributor
    Join Date
    08-24-2011
    Location
    Richmond, Va
    MS-Off Ver
    Excel 2003
    Posts
    130

    Re: Lab Productivity

    Quote Originally Posted by newdoverman View Post
    @BlindAlley
    You mean you want to penalize the employee who goes to work to actually work in favour of those who show up to do the minimum possible

    I re-arranged the data to be 1 record per row and one field per column combining both of your charts.
    Hi Doverman - How did you re-arrange data? Did you type it manually or ???

  10. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Lab Productivity

    I did it manually as it is a "one of" as an example of how to input data so that it is actually useful to create reports with. This was quickly done mostly with copy and paste.
    If you want to quickly enter the dates so that you have 3 of the same date then increment by one day, this formula will do that. It assumes the dates to actually start in A3 and go down the column. A date preceding the actual start date by 1 day is entered in A2.
    Enter this in A3 and fill down and format as a date.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    A
    2
    31/12/2015
    3
    01/01/2016
    4
    01/01/2016
    5
    01/01/2016
    6
    02/01/2016
    7
    02/01/2016
    8
    02/01/2016
    9
    03/01/2016
    10
    03/01/2016
    11
    03/01/2016
    12
    04/01/2016
    13
    04/01/2016
    14
    04/01/2016
    15
    05/01/2016
    16
    05/01/2016
    17
    05/01/2016
    18
    06/01/2016
    19
    06/01/2016
    20
    06/01/2016
    21
    07/01/2016
    22
    07/01/2016
    23
    07/01/2016
    24
    08/01/2016
    25
    08/01/2016

  11. #11
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Lab Productivity

    Please Delete - Post entered twice on save.

  12. #12
    Forum Contributor
    Join Date
    08-24-2011
    Location
    Richmond, Va
    MS-Off Ver
    Excel 2003
    Posts
    130

    Re: Lab Productivity

    Quote Originally Posted by newdoverman View Post
    I did it manually as it is a "one of" as an example of how to input data so that it is actually useful to create reports with. This was quickly done mostly with copy and paste.
    If you want to quickly enter the dates so that you have 3 of the same date then increment by one day, this formula will do that. It assumes the dates to actually start in A3 and go down the column. A date preceding the actual start date by 1 day is entered in A2.
    Enter this in A3 and fill down and format as a date.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    A
    2
    31/12/2015
    3
    01/01/2016
    4
    01/01/2016
    5
    01/01/2016
    6
    02/01/2016
    7
    02/01/2016
    8
    02/01/2016
    9
    03/01/2016
    10
    03/01/2016
    11
    03/01/2016
    12
    04/01/2016
    13
    04/01/2016
    14
    04/01/2016
    15
    05/01/2016
    16
    05/01/2016
    17
    05/01/2016
    18
    06/01/2016
    19
    06/01/2016
    20
    06/01/2016
    21
    07/01/2016
    22
    07/01/2016
    23
    07/01/2016
    24
    08/01/2016
    25
    08/01/2016
    THX AGAIN newdoverman

  13. #13
    Forum Contributor
    Join Date
    08-24-2011
    Location
    Richmond, Va
    MS-Off Ver
    Excel 2003
    Posts
    130

    Re: Lab Productivity

    Quote Originally Posted by newdoverman View Post
    I did it manually as it is a "one of" as an example of how to input data so that it is actually useful to create reports with. This was quickly done mostly with copy and paste.
    If you want to quickly enter the dates so that you have 3 of the same date then increment by one day, this formula will do that. It assumes the dates to actually start in A3 and go down the column. A date preceding the actual start date by 1 day is entered in A2.
    Enter this in A3 and fill down and format as a date.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    A
    2
    31/12/2015
    3
    01/01/2016
    4
    01/01/2016
    5
    01/01/2016
    6
    02/01/2016
    7
    02/01/2016
    8
    02/01/2016
    9
    03/01/2016
    10
    03/01/2016
    11
    03/01/2016
    12
    04/01/2016
    13
    04/01/2016
    14
    04/01/2016
    15
    05/01/2016
    16
    05/01/2016
    17
    05/01/2016
    18
    06/01/2016
    19
    06/01/2016
    20
    06/01/2016
    21
    07/01/2016
    22
    07/01/2016
    23
    07/01/2016
    24
    08/01/2016
    25
    08/01/2016
    THX AGAIN newdoverman

  14. #14
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Lab Productivity

    omer,

    I played with a summary in the attached. I won't elaborate. I just had some fun.
    Attached Files Attached Files
    Dave

+ 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. Productivity formula
    By traxan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-02-2014, 11:15 AM
  2. Measuring Productivity
    By kenjisan70 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-23-2013, 04:58 AM
  3. Productivity
    By mmitri in forum Excel General
    Replies: 3
    Last Post: 09-24-2012, 07:50 PM
  4. Productivity Formula
    By Warehouse Dan in forum Excel General
    Replies: 5
    Last Post: 04-03-2012, 10:01 PM
  5. Productivity formulas
    By angandbri in forum Excel General
    Replies: 2
    Last Post: 09-08-2011, 04:44 AM
  6. Productivity chart
    By mdigeron in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 07-27-2011, 04:27 PM
  7. Productivity Ranking
    By hunter_jack in forum Excel General
    Replies: 5
    Last Post: 12-18-2010, 05:02 PM
  8. Productivity spreadsheet
    By pobrien31 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-13-2005, 04:24 PM

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