SOLVED

# Annual Quarter Hourly production converted to hourly production values

Copper Contributor

# Annual Quarter Hourly production converted to hourly production values

Hello,

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.

2 Replies
best response confirmed by EPSFinance (Copper Contributor)
Solution

# Re: Annual Quarter Hourly production converted to hourly production values

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)

# Re: Annual Quarter Hourly production converted to hourly production values

This worked perfectly, Thank you
1 best response

Accepted Solutions
best response confirmed by EPSFinance (Copper Contributor)
Solution

# Re: Annual Quarter Hourly production converted to hourly production values

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)