Converting 15 minute intervals into hourly values using formulas

H

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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
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
Fluff said:
What version of Excel are you using?
Please update your account details to show this, as it affects what functions you can use.
Click to expand...

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
You must log in or register to reply here.

Similar threads

D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
366
dilshod_k
D
M
  • Solved
pivot data into 15 minute interval counts
Replies
2
Views
6K
mikenz1983
M
M
  • Solved
Finding values between a time range
Replies
3
Views
622
DRSteele
DRSteele
H
  • Question Question
Assigning values to cells in one column based on the values of 2 other columns, please help!
Replies
1
Views
581
Fluff
Fluff
D
  • Solved
Convert to minute elapsed from specific field
Replies
3
Views
688
AlanY
A

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
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