Forum Discussion

Nick_Dirienzo's avatar
Nick_Dirienzo
Copper Contributor
Feb 21, 2020

Excel Function Question

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.

excel
Excel on Mac
Formulas and Functions
office 365

2 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 21, 2020

    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

    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

Resources