Forum Discussion
Collating tricky data
I have one years worth of 1/2hourly electricity usage data. I want to condense it to hourly usage then compare hours of each day of the week in respective months.
I haven’t found a good formula to achieve this. I’m hoping some smart cookie out there will know how to crack the code
12 Replies
The values in the date/time column are text values. I converted them to real date/time values, then created a pivot table.
See the attached version.
Years and months are shown in the Rows area of the pivot table, hour of the day in the Columns area, and sum of usage in the Values area.
Hi Hans,
That's an awesome effort (thank you). I didnt think about pivot tables (I was too focused on vlookups).
My only question now is how I can take the data provided and turn it into days of the week?
I have manually been collating this data below (very time consuming). This is data from the month of February. Below is an example of what I am trying to achieve. See the below graph - the graph shows the average hourly power usage by day.
MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY SUNDAY 12:30:00 AM 8.16 7.72 7.725 8 7.575 7.85 7.76 1:30:00 AM 7.8 7.96 7.9 7.85 7.75 7.675 7.68 2:30:00 AM 7.96 8.08 7.9 8.125 7.725 7.975 7.82 3:30:00 AM 8.52 8.4 7.925 7.75 8.15 8.225 8.12 4:30:00 AM 12.06 12.42 15.1 19.75 17.5 7.95 7.66 5:30:00 AM 21.92 29.9 34.8 24.45 22.225 7.825 7.9 6:30:00 AM 28.58 34.98 40.375 28.225 25.2 8.075 8.52 7:30:00 AM 29.9 33.6 34.9 29.675 29.075 7.975 11.8 8:30:00 AM 34.96 36.1 36.425 31.275 29.825 9.175 11.72 9:30:00 AM 37.22 34.96 35.6 31.125 29.65 9.5 10.58 10:30:00 AM 36.48 34.48 33.675 30.25 28.775 10.025 9.36 11:30:00 AM 37.7 36.1 34.35 33.725 31.075 9.975 9.06 12:30:00 PM 39.58 35.54 35.575 40.475 34 10.825 9.22 1:30:00 PM 40.16 35.78 41.475 40.525 36.4 9.575 10.56 2:30:00 PM 42.88 36.4 39.15 41.3 35.525 9 8.96 3:30:00 PM 40.66 36.26 36.725 39.925 35.8 8.125 8.06 4:30:00 PM 38.4 33.88 32.825 38.3 33.4 7.75 8 5:30:00 PM 28.78 28 26.175 29.85 24.65 7.25 7.16 6:30:00 PM 18.86 19.74 18.6 19.125 17.425 7.85 8.42 7:30:00 PM 15.4 15.12 17.65 16.225 11.4 7.425 8.28 8:30:00 PM 12.36 13.44 12.45 11.15 10.5 7.95 9.02 9:30:00 PM 7.92 9.46 8.75 7.8 9.575 8.075 8.32 10:30:00 PM 7.8 8.14 8 7.65 7.85 8.125 8.48 In the attached version, I have added a Day of Week column to the source data, and used that in the pivot table.
I created a chart based on the pivot table.
