Annual Quarter Hourly production converted to hourly production values

Copper Contributor



I am working on a production report that displays production values on a Quarter hourly basis. I need to be able to convert the quarter hourly data to hourly data. The quarter hourly data is software generated and not able to be changed. In short I need to condense 35040 rows of quarter hourly production down to 8760 rows of hourly production. It would be my preference to use a repeating function.


For reference, the date is column I starting Row 28 (I28), Time is Column J Row 28 (J28), and production values start column K Starting Row 28 (K28). I am trying to condense the production starting cell W28. The image will show some manual calculations for the purpose of example.Quarter Hour to Hour image.png

2 Replies
best response confirmed by EPSFinance (Copper Contributor)


In W28:


=SUMPRODUCT(J$28:J$35067, ($H$28:$H$35067=$U28)*(HOUR($I$28:$I$35067)=HOUR($V28)))


Fill to the right to AA28, then down to row 8787 (or vice versa)

This worked perfectly, Thank you