Excel - get the average value of data in column B when they correspond to a specific time-interval

Question

I have a set of data which is similar to the table below.

Column A	Column B
12:00	200
12:30	235
12:45	233
12:55	245
13:09	265
13:15	288
13:35	289
13:55	299

What I need is to set a formula which allows me to get the max value from column B for each 1 hr interval in column A. For example, from 12:00 to 12:55 the max value is 245. From 13:09 to 13:55 the max value is 299.

In this way I can create another table where I will have in one column the 1st hr interval and in another column the correspondent max value for that hour.

Since I have an extensive amount of data, how can make this easier with a formula?

Thanks

I did this manually, but it takes too long.

Answer 1

Copy, paste, and update A3:B35 in the formula with your array:

=LET(
    data, A3:B35,
    a, TAKE(data, , 1),
    b, DROP(data, , 1),
    ua, UNIQUE(HOUR(a)),
    HSTACK(ua, MAP(ua, LAMBDA(m, MAX(FILTER(b, HOUR(a) = m)))))
)

Answer 2

If all you need is a summary by hour, you can use a Pivot Table. Put the time column in the Rows area of the Pivot Table (as of at least Excel 2016, Excel will automatically generate a Hours field calculated from a time field added to pivot table rows or columns), but the values column in the Values area of the Pivot Table. Change "Summarize value field by" to "Max" in the "Value Field Settings" Dialog.