Find Max of every 30min interval

M

MAP77

Board Regular
Joined
Sep 19, 2018
Messages
60
Office Version
  1. 365
Platform
  1. Windows
Hi everyone.
I've got interval data at 5 min increments and I want to find the Max Current (column c) for every 30 mins.
Eg. out of the first 30 mins, A2:A7 find the max and record as 1/8/22 0:30

Find Max of every 6 rows.xlsx
ABCDE
1TimestampChannel NameMax Current (A)30 min interval timestampsMax Current (A)
21/08/2022 0:05AC15.0861/08/2022 0:309.347
31/08/2022 0:10AC15.0741/08/2022 1:005.124
41/08/2022 0:15AC10.4951/08/2022 1:30
51/08/2022 0:20AC15.0621/08/2022 3:00
61/08/2022 0:25AC19.3471/08/2022 3:30
71/08/2022 0:30AC15.1211/08/2022 4:00
81/08/2022 0:35AC15.1071/08/2022 4:30
91/08/2022 0:40AC15.1241/08/2022 6:30
101/08/2022 0:45AC14.8741/08/2022 7:00
111/08/2022 0:50AC14.851/08/2022 7:30
121/08/2022 0:55AC14.8551/08/2022 9:30
131/08/2022 1:00AC11.5381/08/2022 10:00
141/08/2022 1:05AC11.6521/08/2022 10:30
151/08/2022 1:10AC11.9691/08/2022 12:30
161/08/2022 1:15AC11.8881/08/2022 13:00
171/08/2022 1:20AC11.8751/08/2022 13:30
181/08/2022 1:25AC11.7281/08/2022 15:30
191/08/2022 1:30AC11.7041/08/2022 16:00
201/08/2022 1:35AC11.5391/08/2022 16:30
211/08/2022 1:40AC11.5331/08/2022 18:30
221/08/2022 1:45AC11.5331/08/2022 19:00
231/08/2022 1:50AC11.541/08/2022 19:30
241/08/2022 1:55AC11.8041/08/2022 21:30
251/08/2022 2:00AC11.9161/08/2022 22:00
261/08/2022 2:05AC11.9231/08/2022 22:30
271/08/2022 2:10AC11.9582/08/2022 0:30
281/08/2022 2:15AC11.8692/08/2022 1:00
291/08/2022 2:20AC11.5322/08/2022 1:30
301/08/2022 2:25AC11.5372/08/2022 3:30
311/08/2022 2:30AC11.542/08/2022 4:00
321/08/2022 2:35AC11.5342/08/2022 4:30
331/08/2022 2:40AC11.5422/08/2022 6:30
341/08/2022 2:45AC11.7992/08/2022 7:00
351/08/2022 2:50AC11.7492/08/2022 7:30
361/08/2022 2:55AC11.922/08/2022 9:30
371/08/2022 3:00AC117.1232/08/2022 10:00
381/08/2022 3:05AC116.6722/08/2022 10:30
Sheet1
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Try:

Book1
ABCDE
1TimestampChannel NameMax Current (A)30 min interval timestampsMax Current (A)
212:05:00 AMAC15.08612:30:00 AM9.347
312:10:00 AMAC15.0741:00:00 AM5.124
412:15:00 AMAC10.4951:30:00 AM1.969
512:20:00 AMAC15.0623:00:00 AM1.916
612:25:00 AMAC19.3473:30:00 AM1.958
712:30:00 AMAC15.1214:00:00 AM17.123
812:35:00 AMAC15.1074:30:00 AM16.672
912:40:00 AMAC15.1246:30:00 AM0
1012:45:00 AMAC14.8747:00:00 AM0
1112:50:00 AMAC14.857:30:00 AM0
1212:55:00 AMAC14.8559:30:00 AM0
131:00:00 AMAC11.53810:00:00 AM0
141:05:00 AMAC11.65210:30:00 AM0
151:10:00 AMAC11.96912:30:00 PM0
161:15:00 AMAC11.8881:00:00 PM0
171:20:00 AMAC11.8751:30:00 PM0
181:25:00 AMAC11.7283:30:00 PM0
191:30:00 AMAC11.7044:00:00 PM0
201:35:00 AMAC11.5394:30:00 PM0
211:40:00 AMAC11.5336:30:00 PM0
221:45:00 AMAC11.5337:00:00 PM
231:50:00 AMAC11.547:30:00 PM
241:55:00 AMAC11.8049:30:00 PM
252:00:00 AMAC11.91610:00:00 PM
262:05:00 AMAC11.92310:30:00 PM
272:10:00 AMAC11.95812:30:00 AM
282:15:00 AMAC11.8691:00:00 AM
292:20:00 AMAC11.5321:30:00 AM
302:25:00 AMAC11.5373:30:00 AM
312:30:00 AMAC11.544:00:00 AM
322:35:00 AMAC11.5344:30:00 AM
332:40:00 AMAC11.5426:30:00 AM
342:45:00 AMAC11.7997:00:00 AM
352:50:00 AMAC11.7497:30:00 AM
362:55:00 AMAC11.929:30:00 AM
373:00:00 AMAC117.12310:00:00 AM
383:05:00 AMAC116.67210:30:00 AM
Sheet10
Cell Formulas
RangeFormula
E2:E21E2=SUBTOTAL(4,OFFSET(C2,SEQUENCE(20,,0,6),0,6))
Dynamic array formulas.


Where the 20 is the number of intervals you want to examine.
 
Upvote 0
Thanks Eric.
This seems ok for the first 20. In my actual data Column D has 747 rows to bring me to 31/8/22 and column C has 26778 rows. Which number do I use to replace the 20? I've tried both separately but it doesn't work as you get towards the end of the data.
 
Upvote 0
If column C has 26778 rows, and you want the max of every subset of 6, you'd use 26778/6 = 4463. If column D only has 747 rows, then you could use 747, but it would not calculate all the rows in C past 4482.
 
Upvote 0
Hi Eric,
I modified the formula as above. E2=SUBTOTAL(4,OFFSET(C2,SEQUENCE(4462,,0,6),0,6))

I'm wondering why Column E is returning values after 31/08/2022 23:30? (see second image) The Value for E748 should = 7.684 which is the max value for this range of 6 data.

1662551190802.png


1662551131455.png
 

Attachments

  • 1662550897714.png
    1662550897714.png
    47.5 KB · Views: 2
Upvote 0
Is there a Maxif type of formula to find the max of every 6th row?
 
Upvote 0
There seems to be a discrepancy in the formula, plus I think there's a bit of a misunderstanding somewhere. First, the E748 cell should handle the range of (748 - 1)*6 + 2 to (748 - 1)*6 + 7 or rows 4484 to 4489, not 26768 to 26773. That range should have its max in E4463, which is 1 more than the constant you put in the formula. But even accounting for that, there were some discrepancies. I think I could have adapted the existing formula, but I thought your idea of using MAXIFS might actually be better. Consider this:

Book1
ABCDEFGHIJ
1TimestampChannel NameMax Current (A)30 min interval timestampsMax Current (A)30 min interval start timeMax Current (A)
201/08/2022 00:05AC15.08601/08/2022 00:309.34701/08/2022 00:009.347
301/08/2022 00:10AC15.07401/08/2022 01:005.12401/08/2022 00:305.124
401/08/2022 00:15AC10.49501/08/2022 01:301.96901/08/2022 01:001.969
501/08/2022 00:20AC15.06201/08/2022 03:001.91601/08/2022 01:301.804
601/08/2022 00:25AC19.34701/08/2022 03:301.95801/08/2022 02:001.958
701/08/2022 00:30AC15.12101/08/2022 04:0017.12301/08/2022 02:301.92
801/08/2022 00:35AC15.10701/08/2022 04:3016.67201/08/2022 03:0017.123
901/08/2022 00:40AC15.12401/08/2022 06:30001/08/2022 03:300
1001/08/2022 00:45AC14.87401/08/2022 07:00001/08/2022 04:000
1101/08/2022 00:50AC14.8501/08/2022 07:30001/08/2022 04:300
1201/08/2022 00:55AC14.85501/08/2022 09:30001/08/2022 05:000
1301/08/2022 01:00AC11.53801/08/2022 10:00001/08/2022 05:300
1401/08/2022 01:05AC11.65201/08/2022 10:30001/08/2022 06:000
1501/08/2022 01:10AC11.96901/08/2022 12:30001/08/2022 06:300
1601/08/2022 01:15AC11.88801/08/2022 13:00001/08/2022 07:000
1701/08/2022 01:20AC11.87501/08/2022 13:30001/08/2022 07:300
1801/08/2022 01:25AC11.72801/08/2022 15:30001/08/2022 08:000
1901/08/2022 01:30AC11.70401/08/2022 16:00001/08/2022 08:300
2001/08/2022 01:35AC11.53901/08/2022 16:30001/08/2022 09:000
2101/08/2022 01:40AC11.53301/08/2022 18:30001/08/2022 09:300
2201/08/2022 01:45AC11.53301/08/2022 19:00001/08/2022 10:000
2301/08/2022 01:50AC11.5401/08/2022 19:30001/08/2022 10:300
2401/08/2022 01:55AC11.80401/08/2022 21:30001/08/2022 11:000
2501/08/2022 02:00AC11.91601/08/2022 22:00001/08/2022 11:300
2601/08/2022 02:05AC11.92301/08/2022 22:30001/08/2022 12:000
2701/08/2022 02:10AC11.95802/08/2022 00:30001/08/2022 12:300
2801/08/2022 02:15AC11.86902/08/2022 01:00001/08/2022 13:000
2901/08/2022 02:20AC11.53202/08/2022 01:30001/08/2022 13:300
3001/08/2022 02:25AC11.53702/08/2022 03:30001/08/2022 14:000
3101/08/2022 02:30AC11.5402/08/2022 04:00001/08/2022 14:300
3201/08/2022 02:35AC11.53402/08/2022 04:30001/08/2022 15:000
3301/08/2022 02:40AC11.54202/08/2022 06:30001/08/2022 15:300
3401/08/2022 02:45AC11.79902/08/2022 07:00001/08/2022 16:000
3501/08/2022 02:50AC11.74902/08/2022 07:30001/08/2022 16:300
3601/08/2022 02:55AC11.9202/08/2022 09:30001/08/2022 17:000
3701/08/2022 03:00AC117.12302/08/2022 10:00001/08/2022 17:300
3801/08/2022 03:05AC116.67202/08/2022 10:30001/08/2022 18:000
39001/08/2022 18:300
Sheet10
Cell Formulas
RangeFormula
E2:E4463E2=SUBTOTAL(4,OFFSET(C2,SEQUENCE(4462,,0,6),0,6))
H2:I101H2=LET(start,DATEVALUE("Aug 1, 2022"),Numrows,100,diff,TIMEVALUE("0:30:00"),seq,SEQUENCE(Numrows,,start,diff),max,MAXIFS(C:C,A:A,">="&seq,A:A,"<"&seq+diff),CHOOSE({1,2},seq,max))
Dynamic array formulas.


The E2 formula is the original suggestion, the H2 the new formula. The new formula creates 2 columns, one with the start time of the range, and the second with the max for that range. That should make it easier to compare. And as I mentioned, on row 5 the results are different on the 2 formulas, with the new formula having the correct answer (1.916 is in the next range). As you can see, the new formula uses LET, which lets you define some constants. The start value is uses DATEVALUE to pick the starting date (midnight assumed), numrows is the number of rows you want to show (I assume you want to change that to 4462), and diff is the time difference, or 30 minutes here. Finally, the MAXIFS uses the timestamps in column A to get the max of each 30 minute period.

Instead of entering the 4462, I could have the formula figure out how many rows there are if you want, if you expect to add rows later. Also, since Excel stores times as a fraction of a day, there's a slight chance that the comparison could be off on some rows. We can fix that by adding in a second in the comparison.

Let me know if this works better!
 
Upvote 0
You must log in or register to reply here.

Similar threads

PS_Richard
  • Solved
Counting Number of Unique Instances To Work Out Number Of Cycles
Replies
3
Views
522
Fluff
Fluff
M
  • Solved
Return Value for Day of Week Interval based on Start / Stop time
Replies
2
Views
414
Marklarbear
M
D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
139
dilshod_k
D
MrDB4Excel
  • Question
List of Dates with Duplicate Values
Replies
6
Views
881
MrDB4Excel
MrDB4Excel
P
  • Question
Count all overlapping days from range between another set of dates
Replies
1
Views
2K
Pepperoni
P

Forum statistics

Threads
1,214,591
Messages
6,120,429
Members
448,961
Latest member
nzskater

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