Calculate average of x highest hourly values per day

[#1]

We are looking to average the highest x hourly values in a given day across large data sets. For example, the average of the 12 highest hourly values within a given calendar day over a five-year period. The data include date, hour, and value for various metrics. Thank you for any suggestions.

[#2 · Accepted solution]

There are many options for this:

1. Summary tables (and formulas)
2. Formulas with Col functions
3. Scripting

Attached is one option on how to do it with couple of formulas, first calculate rank of data for date (col number and col rank) and then based on that take top x (in example 3) and calculate average of those (col mean with if statement)

[#3]

Works great - any suggestions on how best to do it by "group" (or location, for example)? I have several sampling locations that have hourly values on the same date.

[#4]

You should be able to add more grouping variables to the Col functions

[#5]

Thanks for the help @jthi - don't know if I'm too novice but I can't seem to get it right.

Here is the formula that I'm attempting to group by "Site" in addition to date.

(1 + Col Number( 1, :Date )) - Col Rank( :"Gas"n, :Date )