making a weighted average in a pivot table

[shadestreet]

I am trying to calculate a weighted average of "orders per hour" using a pivot table.

Currently my pivot table is calculating the "average" of "orders per hour" from my source data which to keep things simple has this structure:

Columns:
A - Employee name
B - Hours worked
C - Orders
D - Returns
E - Orders/Hr (formula: C divided by B)
F - Returns/Hr (formula: D / B)
G - Date

Now if an employee takes only orders for a shift he/she will have a single row entered for each day worked. Likewise if an employee only takes returns that day he/she will have a single row for the returns. Unless they work partial shift the hours worked defaults to 8.

However, if an employee takes both Orders and Returns in a single day he/she will have two rows inserted to the table, one row for each activity with the hours split between the two rows based on how much time was spent.

Now if I were to add a calculated field to my pivot table and take "orders / hours" then I would not get a true average because ALL the hours would be included in the denominator, not just those hours associated with the rows that had orders in them. Therefore I just take the average of column E in my pivot table which doesn't give me a weighted average.

Any ideas on how to get a weighted average to appear in my pivot table?

[carlossaltz]

Go to this link. It helped me.

http://exceltips.vitalnews.com/Pages...ivotTable.html