IF statement or MACRO? Time series math help

B

Benny3

New Member
Joined
Aug 4, 2015
Messages
5
Hi,

I have a time series data set (see table below for example) where I need to add up a column (F:kWh_calc) for just one days worth of data. The data set is years worth of electrical demand interval data and its all at random time stamp intervals that average about every 15 minutes for a recording.

My previous data sets where exact time intervals where I could just use a function to look at the time stamp, and if there was a difference in the 'days,' the function would simply add up the previous 96 rows of data. (96 rows on exactly 15 minute intervals = 1 days data) Ultimately I would then use the filter function in excel to create to create a set of data of the F:kWh_calc. (kilowatts of electricity consumed in one day)

Would anyone know how I can improve this function where I could add up values in a column for just one days worth of data regardless if the time stamps are at different intervals? Columns labeled Month, Weekday, Year, Hour, and Weeknumber are just simple Excel functions to return a value based on Column A:Time.

This function however based on adding up the 96 cells wont work, would anyone know how make it better? Ideally if I was really good at writing code I could create a macro to output the data I want on a separate tab with time stamp for the date of total kWh_calc per day.

=IF(H2<>H3,SUM(F2:F97),"")


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]time[/TD]
[TD]OAT[/TD]
[TD]kW[/TD]
[TD]Minutes_calc[/TD]
[TD]HOUR_calc[/TD]
[TD]kWh_calc[/TD]
[TD]Month[/TD]
[TD]Weeday[/TD]
[TD]Year[/TD]
[TD]Hour[/TD]
[TD]Weeknumber[/TD]
[TD]Holiday[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl65, width: 104"]6/17/2016 5:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 35"]
<tbody>[TR]
[TD="class: xl65, width: 35"]59.9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 42"]
<tbody>[TR]
[TD="class: xl65, width: 42"]31[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]15[/TD]
[TD].25[/TD]
[TD]7.75[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]2016[/TD]
[TD]5[/TD]
[TD]25[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl65, width: 104"]6/17/2016 5:15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 35"]
<tbody>[TR]
[TD="class: xl65, width: 35"]59.9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 42"]
<tbody>[TR]
[TD="class: xl65, width: 42"]29.2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]15[/TD]
[TD].25[/TD]
[TD]7.3[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]2016[/TD]
[TD]5[/TD]
[TD]25[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl65, width: 104"]6/17/2016 5:30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 35"]
<tbody>[TR]
[TD="class: xl65, width: 35"]59.9[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]28.8
[/TD]
[TD]5[/TD]
[TD].0833[/TD]
[TD]2.4[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]2016[/TD]
[TD]5[/TD]
[TD]25[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl65, width: 104"]6/17/2016 5:35[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 35"]
<tbody>[TR]
[TD="class: xl65, width: 35"]59.2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]28.8[/TD]
[TD]10[/TD]
[TD].1667[/TD]
[TD]4.8[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]2016[/TD]
[TD]5[/TD]
[TD]25[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl65, width: 104"]6/17/2016 5:45[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 35"]
<tbody>[TR]
[TD="class: xl65, width: 35"]59.2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]29.8[/TD]
[TD]10[/TD]
[TD].1667[/TD]
[TD]4.967[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]2016[/TD]
[TD]5[/TD]
[TD]25[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl65, width: 104"]6/17/2016 5:55[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 35"]
<tbody>[TR]
[TD="class: xl65, width: 35"]59.7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]28.8[/TD]
[TD]5[/TD]
[TD].0833[/TD]
[TD]2.483[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]2016[/TD]
[TD]5[/TD]
[TD]25[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl65, width: 104"]6/17/2016 6:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 35"]
<tbody>[TR]
[TD="class: xl65, width: 35"]59.7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]28.4[/TD]
[TD]15[/TD]
[TD].25[/TD]
[TD]7.2[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]2016[/TD]
[TD]5[/TD]
[TD]25[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl65, width: 104"]6/17/2016 6:15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 35"]
<tbody>[TR]
[TD="class: xl65, width: 35"]58.6[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]28.4[/TD]
[TD]15[/TD]
[TD].25[/TD]
[TD]7.1[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]2016[/TD]
[TD]5[/TD]
[TD]25[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for your time-!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
You must log in or register to reply here.

Similar threads

S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
470
Sundance_Kid
S
T
  • Solved
Filter Function Not Working As Expected
Replies
4
Views
85
TellM1955
T
S
  • Question Question
Shifting cell reference
Replies
5
Views
113
Cubist
Cubist
M
  • Question Question
Macro or Power Automate to Identify and Move "Washes" from the "Master" Sheet to the "Washes" Sheet
Replies
0
Views
192
Madam_Xavier
M
Cliffork
  • Solved
Print to PDF Macro, or possibly more
2 3
Replies
21
Views
393
mumps
mumps

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top