I am trying to separate and then add 2 years of hourly data to have monthly totals of peak and off peak numbers. The data comes in a .csv file formatted like this and has 17,520 rows of data.
UsageDate | HourNum | Usage |
4/13/2013 | 1 | 58.9 |
4/13/2013 | 2 | 56.84 |
4/13/2013 | 3 | 56.22 |
4/13/2013 | 4 | 55.55 |
4/13/2013 | 5 | 55.25 |
4/13/2013 | 6 | 56.21 |
4/13/2013 | 7 | 60.69 |
4/13/2013 | 8 | 63.9 |
What I do currently is that I paste this formula through the entire column e (D is other data)
=IF(AND(B4>7,B4<22,((WEEKDAY(A4,2))<6)),"P","OP")
which then fills column d with either "p" or "op" depending on the timestamp.
I paste this formula into columns f and g
=IF(E4="P",C4,"--")
=IF(E4="OP",C4,"--")
This makes column f contain only peak numbers while column g contains only off peak numbers.
I then use auto filter to filter a month separately and copy paste it onto a new tab. On that tab I paste a formula to sum each column to give me the total for each month and then paste those to a separate tab where I would have all the monthly numbers.
This works but is a somewhat tedious process. Is there a simple way to get it done automatically or a simpler way to do it?