getting the correct average in pivot table/chart

[aamer]

I have data for 7 days. Usage in one column and Hour slabs in the other,
on 24 hr basis. The data I get is like this

Date Hour Total Duration - Mins
01-Oct 07:00 - 07:59 0
01-Oct 09:00 - 09:59 65.08
01-Oct 10:00 - 10:59 97.12
01-Oct 11:00 - 11:59 111.07
01-Oct 12:00 - 12:59 132.4
01-Oct 13:00 - 13:59 71.65
01-Oct 14:00 - 14:59 96.87
01-Oct 15:00 - 15:59 54.8
01-Oct 16:00 - 16:59 107.1
01-Oct 17:00 - 17:59 70.8
01-Oct 18:00 - 18:59 22.02
01-Oct 19:00 - 19:59 7.07
01-Oct 20:00 - 20:59 12.33
01-Oct 21:00 - 21:59 17.78
01-Oct 23:00 - 23:59 3.33
02-Oct 07:00 - 07:59 3.05
02-Oct 08:00 - 08:59 3.65
02-Oct 09:00 - 09:59 0
02-Oct 10:00 - 10:59 5.35
02-Oct 11:00 - 11:59 43.97
02-Oct 12:00 - 12:59 1.03
02-Oct 13:00 - 13:59 4.98
02-Oct 14:00 - 14:59 2
02-Oct 15:00 - 15:59 5.48
02-Oct 16:00 - 16:59 10.68
02-Oct 17:00 - 17:59 7.45
02-Oct 18:00 - 18:59 24.4
02-Oct 19:00 - 19:59 6.87
02-Oct 20:00 - 20:59 8.93
02-Oct 21:00 - 21:59 5.03
02-Oct 22:00 - 22:59 8.25

I am using Pivot table and chart, when I try to get the average for a
particular hour, it averages the usage by dividing the sum of usage for
the days by the number of records available, instead of dividing it by
the number of days, say, if there are 3 records for period 00:00 –
00:59 in a week, the results if I average out will be by dividing the
usage minutes by 3 instead of by seven. Is there a way that the data
can be rearranged, with little effort or some way that pivot table
gives the correct data.

All help is welcome.

Regards
aamer

 

[Dave Peterson]

The first two thoughts I had were:

Build some dummy records to fill out the rest of the week for each hour (with a
duration of 0). And add those dummy records to your raw data. But this sounds
like a pain.

How about just adding another column that has a formula:

=c2/7
(replace 7 with the number of days you really want)

And drag down.

Then use "Sum of" to get the average.

=sum(all the numbers) / 7
will be equal to:
=sum(all the numbers/7)