Averaging subtotals
 

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

Averaging subtotals
 

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

On Mar 24, 4:46 pm, Dan wrote:
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

Averaging subtotals
 

Pete,

Thanks very much. Your suggestions were very helpful.

Dan

"Pete_UK" wrote:

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

On Mar 24, 4:46 pm, Dan wrote:
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