Home

Excel Function Question

%3CLINGO-SUB%20id%3D%22lingo-sub-1188021%22%20slang%3D%22en-US%22%3EExcel%20Function%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1188021%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20air%20pollution%20measurements%20taken%20every%20second%20and%20I%20am%20looking%20to%20find%20a%20way%20to%20achieve%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20Find%20the%20median%20of%20every%209th%2C%2010th%2C%20and%2011th%20value%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20a%20bit%20hard%20for%20me%20to%20describe%20but%20what%20I%20mean%20is%20I%20want%20to%20find%20the%20median%20value%20of%20the%209th%2C%2010th%2C%20and%2011th%20values%2C%20the%20median%20value%20of%20the%2019th%2C%2020th%2C%20and%2021st%20values%2C%20the%20median%20value%20of%20the%2029th%2C%2030th%2C%2031st%20values%2C%20and%20so%20on%20for%20my%20entire%20dataset.%20I'm%20looking%20for%20a%20function%20to%20complete%20this%20calculation%20as%20my%20dataset%20has%20about%208%2C000%20rows%20of%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1188021%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1188039%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Function%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1188039%22%20slang%3D%22en-US%22%3EYou%20van%20use%20the%20MEDIAN%20function%20to%20achieve%20the%20task%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1188177%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Function%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1188177%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F565410%22%20target%3D%22_blank%22%3E%40Nick_Dirienzo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20didn't%20catch%20you'd%20like%20to%20find%20median%20for%20all%20such%20cells%20together%2C%20or%20for%20each%203%20separately.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20324px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F172624i114F3C46C80212A4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eif%20first%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DAGGREGATE(19%2C6%2C1%2F(MOD(ROW(%24B%242%3A%24B%2445)-ROW(%24B%241)-2%2C10)%26gt%3B6)%2F(ROW(%24B%242%3A%24B%2445)%26gt%3BROW(%24B%241)%2B1)*%24B%242%3A%24B%2445%2C2)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eif%20second%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DMEDIAN(INDEX(%24B%242%3A%24B%2445%2C(ROW()-ROW(%24F%241)-1)*10%2B%7B9%2C10%2C11%7D))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20down%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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
Highlighted
You van use the MEDIAN function to achieve the task
Highlighted

@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

Related Conversations
Fixieren in Exel
nicoalex12 in Excel on
0 Replies
Combine/Rearrange Rows and Columns
Rob Nunley in Excel on
0 Replies
Excel: Condensing and Counting
MADavis in Excel on
0 Replies
VBA code to reset togglebutton colors.
dick_hollister in Excel on
0 Replies
Horizontal Scrolling
stephen607 in Excel on
0 Replies
How to in Excel
HarryNetherlands in Excel on
1 Replies