Converting 15 minute intervals into hourly values using formulas

Hawk11ns

Board Regular
Joined
Jul 21, 2015
Messages
61
Office Version
  1. 365
Platform
  1. Windows
I have daily data that is published every 15-minute interval, for a total of 96 intervals each day, and I need to convert these to hourly averages in a more straight-forward fashion. Historically, starting with the first interval of the day 0015, I would add a '-' symbol in a new column next do the pricing data for the first three intervals (xx15/xx30/xx45) and then place the AVERAGE formula in the 4th interval (xx00) . I would then copy these 4 cells and past down through interval 96, resulting in a '-' symbol for the first three intervals of each hour and the average value for that hour on the fourth interval. I would then filter the data by removing the '-' values and copy/pasting the displayed 24-hour data into a new sheet so all that is left are 24 hourly averages. The only alternative I can imagine, right now, is to create an hourly table and write hourly AVERAGE formulas for each hour, but I would need to tie them to the respective rows and this could leave us me prone to human error. I guess I could also write a macro to do all this for me, but for the sake of increasing my excel knowledge and skill set, I'd like to see if there is anything I am not considering that can produce the same results in a much more simplistic fashion. Thanks!

Oper DayInterval EndingHB_BUSAVGOper DayHour EndingHB_BUSAVG
8/18/202015$ 18.648/18/20201:00
8/18/202030$ 18.238/18/20202:00
8/18/202045$ 17.978/18/20203:00
8/18/2020100$ 17.658/18/20204:00
8/18/2020115$ 17.428/18/20205:00
8/18/2020130$ 17.108/18/20206:00
8/18/2020145$ 16.968/18/20207:00
8/18/2020200$ 16.768/18/20208:00
8/18/2020215$ 16.728/18/20209:00
8/18/2020230$ 16.598/18/202010:00
8/18/2020245$ 16.498/18/202011:00
8/18/2020300$ 16.478/18/202012:00
8/18/2020315$ 16.458/18/202013:00
8/18/2020330$ 16.468/18/202014:00
8/18/2020345$ 16.398/18/202015:00
8/18/2020400$ 16.348/18/202016:00
8/18/2020415$ 16.378/18/202017:00
8/18/2020430$ 16.428/18/202018:00
8/18/2020445$ 16.468/18/202019:00
8/18/2020500$ 16.528/18/202020:00
8/18/2020515$ 16.638/18/202021:00
8/18/2020530$ 16.908/18/202022:00
8/18/2020545$ 17.068/18/202023:00
8/18/2020600$ 17.258/18/20200:00
8/18/2020615$ 17.71
8/18/2020630$ 17.85
8/18/2020645$ 17.83
8/18/2020700$ 17.73
8/18/2020715$ 17.65
8/18/2020730$ 17.78
8/18/2020745$ 17.84
8/18/2020800$ 17.89
8/18/2020815$ 17.90
8/18/2020830$ 18.01
8/18/2020845$ 18.07
8/18/2020900$ 18.16
8/18/2020915$ 18.48
8/18/2020930$ 18.93
8/18/2020945$ 19.24
8/18/20201000$ 19.61
8/18/20201015$ 21.01
8/18/20201030$ 23.77
8/18/20201045$ 25.25
8/18/20201100$ 25.71
8/18/20201115$ 24.44
8/18/20201130$ 24.82
8/18/20201145$ 30.03
8/18/20201200$ 26.87
8/18/20201215$ 26.89
8/18/20201230$ 29.68
8/18/20201245$ 49.61
8/18/20201300$ 40.36
8/18/20201315$ 28.33
8/18/20201330$ 35.19
8/18/20201345$ 44.35
8/18/20201400$ 56.39
8/18/20201415$ 30.62
8/18/20201430$ 33.95
8/18/20201445$ 41.77
8/18/20201500$ 38.10
8/18/20201515$ 83.17
8/18/20201530$ 180.88
8/18/20201545$ 167.00
8/18/20201600$ 138.05
8/18/20201615$ 158.00
8/18/20201630$ 101.38
8/18/20201645$ 74.10
8/18/20201700$ 51.64
8/18/20201715$ 71.47
8/18/20201730$ 62.27
8/18/20201745$ 48.36
8/18/20201800$ 34.57
8/18/20201815$ 28.14
8/18/20201830$ 27.64
8/18/20201845$ 27.48
8/18/20201900$ 26.93
8/18/20201915$ 27.06
8/18/20201930$ 32.54
8/18/20201945$ 26.99
8/18/20202000$ 26.49
8/18/20202015$ 26.24
8/18/20202030$ 26.56
8/18/20202045$ 25.04
8/18/20202100$ 23.75
8/18/20202115$ 22.75
8/18/20202130$ 21.92
8/18/20202145$ 20.46
8/18/20202200$ 19.31
8/18/20202215$ 19.67
8/18/20202230$ 19.93
8/18/20202245$ 18.78
8/18/20202300$ 18.12
8/18/20202315$ 17.62
8/18/20202330$ 17.04
8/18/20202345$ 16.85
8/18/20202400$ 16.33
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
What version of Excel are you using?
Please update your account details to show this, as it affects what functions you can use.
 
Upvote 0
What version of Excel are you using?
Please update your account details to show this, as it affects what functions you can use.

Just updated my info - thank you for making me aware of this!
 
Upvote 0
Thanks for that, how about
+Fluff New.xlsm
ABCDEFG
1Oper DayInterval EndingHB_BUSAVGOper DayHour EndingHB_BUSAVG
218/08/20201518.6418/08/2020118.12
318/08/20203018.2318/08/2020217.06
418/08/20204517.9718/08/2020316.57
518/08/202010017.6518/08/2020416.41
618/08/202011517.4218/08/2020516.44
718/08/202013017.118/08/2020616.96
818/08/202014516.9618/08/2020717.78
918/08/202020016.7618/08/2020817.79
1018/08/202021516.7218/08/2020918.04
1118/08/202023016.5918/08/20201019.07
1218/08/202024516.4918/08/20201123.94
1318/08/202030016.4718/08/20201226.54
1418/08/202031516.4518/08/20201336.64
1518/08/202033016.4618/08/20201441.07
1618/08/202034516.3918/08/20201536.11
1718/08/202040016.3418/08/202016142.28
1818/08/202041516.3718/08/20201796.28
1918/08/202043016.4218/08/20201854.17
2018/08/202044516.4618/08/20201927.55
2118/08/202050016.5218/08/20202028.27
2218/08/202051516.6318/08/20202125.40
2318/08/202053016.918/08/20202221.11
2418/08/202054517.0618/08/20202319.13
2518/08/202060017.2518/08/20202416.96
2618/08/202061517.71
2718/08/202063017.85
2818/08/202064517.83
2918/08/202070017.73
3018/08/202071517.65
3118/08/202073017.78
3218/08/202074517.84
3318/08/202080017.89
3418/08/202081517.9
3518/08/202083018.01
3618/08/202084518.07
3718/08/202090018.16
3818/08/202091518.48
3918/08/202093018.93
4018/08/202094519.24
Master
Cell Formulas
RangeFormula
E2:E25E2=INDEX(A2:A25,)
F2:F25F2=SEQUENCE(24)
G2:G25G2=SUMPRODUCT(($B$2:$B$100/100<=F2)*($B$2:$B$100/100>N(F1))*($C$2:$C$100))/4
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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