Weighted Average of Time

Hello all:

I am a little dumbfounded here. Hoping someone can educate me on my middle school algebra again.

I am trying to determine the weighted average of an employees break time. I want an apples to apples comparison of break time percentage as it relates to the total time logged into the system.

For example, let us assume I have an employee who's total login time is 8.5 hours and that employee takes a 30 minute lunch. I then have another employee who's total login time is 9 hours and takes a 1 hour lunch. The net login time for both employees is 8 hours. They both work the same amount of time. The employee who takes the longer lunch shouldn't be punished for taking a longer break if he works the additional time.

For the life of me I can't seem to remember how to calculate the weighted average out to show even percentages. I have attached a spreadsheet with hypothetical hours.

Any help would be appreciated. The old brain isn't working today!

Thank you

EDIT:

I should note that the WT in the spreadsheet means Work Time. So the amount of time worked between IN and OUT.

Why would you want a weighted average? Weighted by what?

Do you care if people work 8h + 1/2h lunch, 8 1/2 + 1h lunch, 9h + 1 1/2h lunch?

I think the problem is not the algebra, it's deciding what you want to measure.

Hi ExcelAteMyHomework and welcome, I like your alias.

I remember one of the Physics Principles that I never believed which was "You Can't Average Percentages".
Here is why http://msdn.microsoft.com/en-us/library/bb687891.aspx

As a math teacher many, many years ago I used a Student T score (like the current ETS Score) for each test as they were averageable. The students, principal and parents never got it, but I was an accurate grader. Look at IQ and SAT standardized test scoring at http://www.eduers.com/sat/scoring_methods.htm

Maybe you are wanting to show performance based on breaks in some other way?

Yeah!!!!!

I agree with shg for once!!

Two People work and take lunch
Both work 2 hours and both take lunch
Person A takes an hour lunch and Person B takes a 2 hour lunch

A = 1 hr lunch / 3 hrs at work = 2 hours / 3 hrs at work = 66% of time at work is work time
B = 2 hr lunch / 4 hrs at work = 2 hours/ 4 hrs at work = 50% of time at work is work time.

Percentages are useless for this problem. They both work 2 hrs. Do you reward the 1 hr lunch or 2 hr lunch?