davideross19
New Member
- Joined
- May 5, 2014
- Messages
- 3
Dear MrExcel forum,
As per many posting, I am lost to understand the resulting instability my formula is producing. I have tried 2 different methods from various posts and both yield the same oscillating summations. I wish to sum the kWh consumption contained in a raw file containing 30min interval data over a complete year (17,520 cells). I wish to sum the data in 1 hour increments (0-24) by calendar months for weekdays only (used: networkdays function). The raw data has the date/time listed in the one cell. I have created a separate column for the time only using MOD(). I have used the following two equations, both yield the same results for a given month.
=SUMIFS($F$6:$F$17526,$D$6:$D$17526,"<="&$R40,$D$6:$D$17526,">"&$R39,$B$6:$B$17526,">="&S$38,$B$6:$B$17526,"<="&S$39,$C$6:$C$17526,">0")
where R$40 = 1/11/2012 (Normally this would be end of the month but have used same day to illustrate with limited data my problem); R39 = 1/11/2012 (First day of the month); S$38 = 12:00:00 AM and S$39 = 1:00:00 AM.
Alternatively I have tried (ignoring the month complication for now)
=SUM(IF((VALUE($D$6:$D$17526)>=R39)*(VALUE($D$6:$D$17526)<=R40)*($C$6:$C$17526=1),$F$6:$F$17526))
Please note this is historical looking data, i.e. the kWh consumption posted at 1:00:00 AM is the sum total kWh consumed in the prevailing 30min interval by the client.
Taking the simple metered data shown below, summing up until 6am, the sum in each 1 hour block ought to be between 5.2-5.4 kWh/hr. If I limit the above equations to this shorter range of numbers I obtain using both methods the same result. I can not understand why I obtain the peaks and troughs (oscillation). I have tried with/without the "=" in the ">=" or vice versa in a consistent manner but this does not change the result. Thank you in advance.
<tbody>
</tbody>
<tbody>
</tbody>
As per many posting, I am lost to understand the resulting instability my formula is producing. I have tried 2 different methods from various posts and both yield the same oscillating summations. I wish to sum the kWh consumption contained in a raw file containing 30min interval data over a complete year (17,520 cells). I wish to sum the data in 1 hour increments (0-24) by calendar months for weekdays only (used: networkdays function). The raw data has the date/time listed in the one cell. I have created a separate column for the time only using MOD(). I have used the following two equations, both yield the same results for a given month.
=SUMIFS($F$6:$F$17526,$D$6:$D$17526,"<="&$R40,$D$6:$D$17526,">"&$R39,$B$6:$B$17526,">="&S$38,$B$6:$B$17526,"<="&S$39,$C$6:$C$17526,">0")
where R$40 = 1/11/2012 (Normally this would be end of the month but have used same day to illustrate with limited data my problem); R39 = 1/11/2012 (First day of the month); S$38 = 12:00:00 AM and S$39 = 1:00:00 AM.
Alternatively I have tried (ignoring the month complication for now)
=SUM(IF((VALUE($D$6:$D$17526)>=R39)*(VALUE($D$6:$D$17526)<=R40)*($C$6:$C$17526=1),$F$6:$F$17526))
Please note this is historical looking data, i.e. the kWh consumption posted at 1:00:00 AM is the sum total kWh consumed in the prevailing 30min interval by the client.
Taking the simple metered data shown below, summing up until 6am, the sum in each 1 hour block ought to be between 5.2-5.4 kWh/hr. If I limit the above equations to this shorter range of numbers I obtain using both methods the same result. I can not understand why I obtain the peaks and troughs (oscillation). I have tried with/without the "=" in the ">=" or vice versa in a consistent manner but this does not change the result. Thank you in advance.
<tbody> </tbody> | ||||||||||||
<tbody>
</tbody>
| DATE=INT(E6) COL B | Weekday COL C | TIME=MOD(E6,1) COL D | Date & time COL E | kWh COL F |
| 1/11/2012 | 1 | 12:30:00 AM | 1/11/2012 0:30 | 2.6 |
| 1/11/2012 | 1 | 1:00:00 AM | 1/11/2012 1:00 | 2.6 |
| 1/11/2012 | 1 | 1:30:00 AM | 1/11/2012 1:30 | 2.8 |
| 1/11/2012 | 1 | 2:00:00 AM | 1/11/2012 2:00 | 2.6 |
| 1/11/2012 | 1 | 2:30:00 AM | 1/11/2012 2:30 | 2.6 |
| 1/11/2012 | 1 | 3:00:00 AM | 1/11/2012 3:00 | 2.7 |
| 1/11/2012 | 1 | 3:30:00 AM | 1/11/2012 3:30 | 2.6 |
| 1/11/2012 | 1 | 4:00:00 AM | 1/11/2012 4:00 | 2.7 |
| 1/11/2012 | 1 | 4:30:00 AM | 1/11/2012 4:30 | 2.6 |
| 1/11/2012 | 1 | 5:00:00 AM | 1/11/2012 5:00 | 2.6 |
| 1/11/2012 | 1 | 5:30:00 AM | 1/11/2012 5:30 | 2.7 |
| 1/11/2012 | 1 | 6:00:00 AM | 1/11/2012 6:00 | 2.5 |
| 1/11/2012 | 1 | 6:30:00 AM | 1/11/2012 6:30 | 2.9 |
| 1/11/2012 | 1 | 7:00:00 AM | 1/11/2012 7:00 | 3.6 |
| 1/11/2012 | 1 | 7:30:00 AM | 1/11/2012 7:30 | 4.7 |
| 1/11/2012 | 1 | 8:00:00 AM | 1/11/2012 8:00 | 6.6 |
| 1/11/2012 | 1 | 8:30:00 AM | 1/11/2012 8:30 | 7.4 |
| 1/11/2012 | 1 | 9:00:00 AM | 1/11/2012 9:00 | 8.2 |
| 1/11/2012 | 1 | 9:30:00 AM | 1/11/2012 9:30 | 11.1 |
| 1/11/2012 | 1 | 10:00:00 AM | 1/11/2012 10:00 | 9.7 |
| 1/11/2012 | 1 | 10:30:00 AM | 1/11/2012 10:30 | 10 |
| 1/11/2012 | 1 | 11:00:00 AM | 1/11/2012 11:00 | 9.8 |
| 1/11/2012 | 1 | 11:30:00 AM | 1/11/2012 11:30 | 10.1 |
| 1/11/2012 | 1 | 12:00:00 PM | 1/11/2012 12:00 | 10.1 |
| 1/11/2012 | 1 | 12:30:00 PM | 1/11/2012 12:30 | 11.2 |
<tbody>
</tbody>
Last edited:
