Tally the figures acquired from the same date

[Brian]

Hi,

I am working on a spreadsheet with electricity meter reading
throughout a year. The reading were taken multiple times on one day.

I tried everything i know to works out the monthly average of the
electricity usage but i failed.
I managed to flag the day and month on each figure and thats as far as
I go so far.
Can anyone think of a formula to do the task.

An extract of the spreadsheet:

day Mth Yr Time V_Ave_PC6
9 9 2008 18:00:00 244.3
9 9 2008 18:30:00 243.4
9 9 2008 19:00:00 243.7
9 9 2008 19:30:00 241.9
9 9 2008 20:00:00 242.6
10 9 2008 20:30:00 242.4
10 9 2008 21:00:00 245
10 9 2008 21:30:00 239.8
10 9 2008 22:00:00 242.7


11 9 2008 20:30:00 240.9
11 9 2008 21:00:00 241.8
11 9 2008 21:30:00 241.3
11 9 2008 22:00:00 241.4
11 9 2008 22:30:00 243.7
11 9 2008 23:00:00 241.8
11 9 2008 23:30:00 243.3
11 9 2008 0:00:00 241.4
11 9 2008 0:30:00 242.2
11 9 2008 1:00:00 242.9
11 9 2008 1:30:00 243.1
11 9 2008 2:00:00 244.2

 

[Pete_UK]

They look like consumption figures, rather than meter readings (where
the difference gives you the consumption). I think you would need a
helper column where you could combine the day, month and year into a
proper date with this:

=DATE(C2,B2,A2)

and then you could get the consumption per day with a SUMIF.

If you just want the consumption for month 9 you could do this:

=SUMIF(B:B,9,E:E)

and then divide by the number of days in that month to get the monthly
average usage.

Hope this helps.

Pete

 

[Brian]

Thx Pete

They look like consumption figures, rather than meter readings (where
the difference gives you the consumption). I think you would need a
helper column where you could combine the day, month and year into a
proper date with this:

=DATE(C2,B2,A2)

and then you could get the consumption per day with a SUMIF.

If you just want the consumption for month 9 you could do this:

=SUMIF(B:B,9,E:E)

and then divide by the number of days in that month to get the monthly
average usage.

Hope this helps.

Pete

Thx

 

[Pete_UK]

You're welcome, Brian - thanks for feeding back.

Pete