Tally the figures acquired from the same date

  • Thread starter Thread starter Brian
  • Start date Start date
B

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
 
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








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
Click to expand...

Thx
 
Back
Top