CODEWITHSUNDEEP
exceldatabaseexcel-formulaanalyticsdata-management
Pliz89
Pliz89

Reputation: 11

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

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.

Upvotes: 0

Views: 112

Answers (2)

robartsd
robartsd

Reputation: 1470

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.

Pivot Table Fields

Value Field Settings

Pivot Table Summarizing Max Value by Hour

Upvotes: 0

Bam
Bam

Reputation: 585

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)))))
)

Picture Reference

Upvotes: 0

Related Questions