Roll-up Year Worth of Power Company

Question

1. columns i want to output are daily sum of (kWh), daily average, daily peak or max, and time of peak. sum, averge and max forulas are straight forward but i struggle rolling them to next block of 24 hr daily data

2. for usage kwh, i want to show max value and peak time (so max and show value left of max), again straight forward enough for 1 day range, but getting formula to comprehend the day range changes is trickier, index?

   a years worth of raw hourly data is 8760 rows, 744 for dec..

3. i can pivot the basic chunks bout would rather have a rollup of key data

hope this makes sense

sample of raw data

Month            Date         Time      Usage(kWh)  
December    12/31/2016  11:00 PM        0.74    
December    12/31/2016  10:00 PM        1.17                
December    12/31/2016  9:00 PM         0.75                
December    12/31/2016  8:00 PM         0.85                
December    12/31/2016  7:00 PM         0.96                
December    12/31/2016  6:00 PM         1.08                
December    12/31/2016  5:00 PM         1.37                
December    12/31/2016  4:00 PM         3.44                
December    12/31/2016  3:00 PM         5.96                
December    12/31/2016  2:00 PM         4.45                
December    12/31/2016  1:00 PM         5.08                
December    12/31/2016  12:00 PM        3.71                
December    12/31/2016  11:00 AM        2.55                
December    12/31/2016  10:00 AM        1.60                
December    12/31/2016  9:00 AM         1.07                
December    12/31/2016  8:00 AM         1.47                
December    12/31/2016  7:00 AM         3.94                
December    12/31/2016  6:00 AM         1.28                
December    12/31/2016  5:00 AM         0.96                
December    12/31/2016  4:00 AM         1.01                
December    12/31/2016  3:00 AM         1.44                
December    12/31/2016  2:00 AM         1.01                
December    12/31/2016  1:00 AM         1.02                
December    12/31/2016  12:00 AM        1.17                
December    12/30/2016  11:00 PM        1.78    
December    12/30/2016  10:00 PM        2.25                
December    12/30/2016  9:00 PM         2.15                
December    12/30/2016  8:00 PM         4.05                
December    12/30/2016  7:00 PM         2.91                
December    12/30/2016  6:00 PM         2.26                
December    12/30/2016  5:00 PM         3.69                
December    12/30/2016  4:00 PM         4.39                
December    12/30/2016  3:00 PM         2.71                
December    12/30/2016  2:00 PM         8.07        

    

Answer 1

1. Use Sumifs with the date as one of the conditions. If you have an Office 365 version of Excel, you can use the new functions Maxifs and AverageIfs and specify the date as one of the conditions. If you don't have Office 365, you can use Sumproduct, which does the job fine, but will be a bit slower with large datasets.

2. Yes, Index is the way to go. You can build an index range for the day based on the first match of the date going to the last match of the date. Then nest that index in a Match formula. =MAX(INDEX(D:D,MATCH(F2,B:B,0)):INDEX(D:D,MATCH(F2-1,B:B,-1)-1)) if the date you want to look up is in cell F2, like in this screenshot

If you get this to work, you can of course also use the approach for the formulas in 1.)

Answer 2

I would also like to suggest you might be better off importing the data into access, and then you can use SQL.

select date,min(kwh),max(kwh), ... from <table name>
order by date
group by date

That gives you most of what you want. The date/time of those things are a bit trickery, but can be done.