Calculate average of x highest hourly values per day
This widget could not be displayed.
Created:
Jun 22, 2022 10:40 AM
| Last Modified: Jun 10, 2023 4:50 PM(741 views)
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.
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)
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)
Re: Calculate average of x highest hourly values per day
This widget could not be displayed.
Created:
Jun 22, 2022 11:18 AM
| Last Modified: Jun 22, 2022 8:18 AM(728 views)
| Posted in reply to message from jthi 06-22-2022
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.