Excel Function Question

Copper Contributor

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.

2 Replies
You van use the MEDIAN function to achieve the task

@Nick_Dirienzo 

I didn't catch you'd like to find median for all such cells together, or for each 3 separately. 

For such sample

image.png

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