Feb 21 2020 01:09 PM
Hi everyone,
I have air pollution measurements taken every second and I am looking to find a way to achieve the following:
- Find the median of every 9th, 10th, and 11th value
It is a bit hard for me to describe but what I mean is I want to find the median value of the 9th, 10th, and 11th values, the median value of the 19th, 20th, and 21st values, the median value of the 29th, 30th, 31st values, and so on for my entire dataset. I'm looking for a function to complete this calculation as my dataset has about 8,000 rows of data.
Feb 21 2020 01:14 PM
Feb 21 2020 02:19 PM
I didn't catch you'd like to find median for all such cells together, or for each 3 separately.
For such sample
if first
=AGGREGATE(19,6,1/(MOD(ROW($B$2:$B$45)-ROW($B$1)-2,10)>6)/(ROW($B$2:$B$45)>ROW($B$1)+1)*$B$2:$B$45,2)
if second
=MEDIAN(INDEX($B$2:$B$45,(ROW()-ROW($F$1)-1)*10+{9,10,11}))
and drag down