+ Reply to Thread
Results 1 to 12 of 12

Productivity Percentage Formula

  1. #1
    Registered User
    Join Date
    04-19-2011
    Location
    Arizona
    MS-Off Ver
    Excel 2010
    Posts
    5

    Productivity Percentage Formula

    I am trying to correct a formula for a productivity worksheet in excel. The idea is that we would have the ability to input the number of hours worked each day and the number of direct hours (working with clients) per day. Then take the daily percentages and add them then devide them to determine the final productivity percentage for the week. Additionally not everyone works a full week so I would need it to only divide the percentages of the days that were completed. Any suggestions? or does anybody know a formula that would accomplish this already? I am good with basic excel and was able to identify a flaw in the current productivity sheet.

  2. #2
    Valued Forum Contributor tlafferty's Avatar
    Join Date
    04-08-2011
    Location
    United States, Tacoma, WA
    MS-Off Ver
    Excel 2010, Excel 2013 Customer Preview
    Posts
    1,112

    Re: Productivity Percentage Formula

    Sure - I'd look into SUMPRODUCT. Can you post a sample book?

  3. #3
    Registered User
    Join Date
    04-19-2011
    Location
    Arizona
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Productivity Percentage Formula

    Ok i have attached a copy of the work sheet. The two rows in question are billable hours and hours worked. The basics are fine in that each day you would divide the billable hours by the number of hours worked to get the productivity for the day. The way the sheet is currently set up to get productivity for the week is that it adds the hours worked for the week and the billable hours for the week and then divides the one by the other however there is a flaw in this math because there are days where you could have a 100% productivity but only have one billable hour for that day due to training. This would change the over all numbers and actually lower your productivity even though you still have a 100% productivity. So if we were able to change the math to take the daily percentages and add them together then divide them by the number of days worked or the number of days calculated (to account for people who work 4 days or have a holiday off) then the weekly productivity would be more accurate.

    For example if you look at the week of 4/11 - 4/15 on thursday you will see billable hours 1 hours worked 1. by adding the hours and billable hours for the week then dividing that in the G column we come up with 49.63. if you change the billable hours to say 8 and the hours worked to 8 you still have the same amount of productivity for the day but it changes the added numbers in G and raises the productivy through that calculation. from 49.63% to 60%. Basically on that thursday i had done 7 hours of training which doesn't count as productivity but it doesn't count against us either so the only billable hours and hours worked for that day were one. But it significantly lowers the productivity for that day.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor tlafferty's Avatar
    Join Date
    04-08-2011
    Location
    United States, Tacoma, WA
    MS-Off Ver
    Excel 2010, Excel 2013 Customer Preview
    Posts
    1,112

    Re: Productivity Percentage Formula

    This might be what you're after:
    Sum percentages (B18:F18)
    Divide that total by regular hours worked that are greater than 0:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    04-19-2011
    Location
    Arizona
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Productivity Percentage Formula

    Ok used to your formula however the #div/0! is still there and as a result it will not total the % row. so for example on that same sheet i put 0 for hours worked and 0 for billable hours and got the above error due to dividing a number by 0. The % row is formula based on dividing row 16 by row 17. I put the formula you gave me in to total column on the % row. if there is a number in there then it works fine. I also tried the #n/a but it still has the same result.

  6. #6
    Forum Contributor day92's Avatar
    Join Date
    04-20-2010
    Location
    Los Angeles
    MS-Off Ver
    Excel 360
    Posts
    600

    Re: Productivity Percentage Formula

    The formula by tlafferty appears to work in cell G5 & G18. Where is not working?

  7. #7
    Valued Forum Contributor tlafferty's Avatar
    Join Date
    04-08-2011
    Location
    United States, Tacoma, WA
    MS-Off Ver
    Excel 2010, Excel 2013 Customer Preview
    Posts
    1,112

    Re: Productivity Percentage Formula

    For your daily sums, use:
    Please Login or Register  to view this content.
    And fill across.

    For your weekly totals use:
    Please Login or Register  to view this content.
    Copy formulas as appropriate.

    See if attached is what you had in mind
    Last edited by tlafferty; 04-26-2011 at 08:05 PM.

  8. #8
    Registered User
    Join Date
    04-19-2011
    Location
    Arizona
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Productivity Percentage Formula

    I worked it out... I used a if cell = 0 then return "0" formula.. Thanks I think this will work great..

  9. #9
    Valued Forum Contributor tlafferty's Avatar
    Join Date
    04-08-2011
    Location
    United States, Tacoma, WA
    MS-Off Ver
    Excel 2010, Excel 2013 Customer Preview
    Posts
    1,112

    Re: Productivity Percentage Formula

    Could you mark the post as solved ?

  10. #10
    Registered User
    Join Date
    04-19-2011
    Location
    Arizona
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Productivity Percentage Formula

    sure where do i do that at?

  11. #11
    Valued Forum Contributor tlafferty's Avatar
    Join Date
    04-08-2011
    Location
    United States, Tacoma, WA
    MS-Off Ver
    Excel 2010, Excel 2013 Customer Preview
    Posts
    1,112

    Re: Productivity Percentage Formula

    Here's how it's done:
    Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.

  12. #12
    Registered User
    Join Date
    04-28-2011
    Location
    Johannesburg
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Productivity Percentage Formula

    Hey there..

    Am in need for some guidance on a workbook.
    I need to allocate work to 7 different people.

    Each time the work is allocated the sheet must tell me what % of work has been allocated to person X

    I have attached an example it just needs formulas...
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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