Converting 15 minute intervals into hourly values using formulas

Hawk11ns

Board Regular
Joined
Jul 21, 2015
Messages
52
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,847
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?
Please update your account details to show this, as it affects what functions you can use.
 

Hawk11ns

Board Regular
Joined
Jul 21, 2015
Messages
52
Office Version
  1. 365
Platform
  1. Windows
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,847
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,387
Messages
5,595,879
Members
414,029
Latest member
mrwilker

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
Top