Averaging subtotals

D

Dan

I have four columns: date (A), number of hours (B), amount charged (C), and
hourly rate (D). Column D is column C divided by column B. I can have
multiple rows with the same date. I want to subtotal column B and column C by
date and then divide the subtotal of column C by the subtotal of column B to
get the average hourly rate for each date. I can't see how to do this. Can
someone help me? Thanks!

Dan
 
P

Pete_UK

If your data is in date order and has a heading row at the top, then
you can apply Data | Subtotals to obtain subtotals at each change of
date. Then you can apply autofilter to column A and select Custom |
Contains | Total and this will display only the subtotals rows. Enter
a formula in column E to obtain your average rate and then copy this
down the visible cells. Finally, you can select All on the filter pull-
down.

Hope this helps.

Pete
 
Top