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.