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.

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

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.

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.

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.

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

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

Could you mark the post as solved ?

sure where do i do that at?

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.

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...