SOLVED

Please Help !! Median Range Formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1538812%22%20slang%3D%22en-US%22%3EPlease%20Help%20!!%20Median%20Range%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1538812%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20-%20I%20am%20trying%20to%20get%20the%20median%20of%20a%20large%20number%20of%20values%20in%20a%20column%20range%20example%20rows%20(c.3000)-%20such%20that%20the%20formula%20ignores%20zero%20%2F%20errors%20or%20blanks.%20Additionally%2C%20I%20want%20to%20be%20able%20to%20apply%20a%20filter%20and%20that%20the%20result%20is%20just%20using%20the%20filtered%20visible%20dataset.%20I%20have%20used%20the%20formula%20below%2C%20but%20it%20is%20giving%20me%20a%20value%20I%20don't%20think%20is%20correct.%26nbsp%3B%20Any%20help%20would%20be%20really%20appreciated%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DMEDIAN(IF(SUBTOTAL(2%2COFFSET(V9%2CROW(V9%3AV2999)-ROW(V9)%2C0))%2CV9%3AV2999))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESometimes%20this%20gives%20value%20and%20sometimes%20a%20blank%20and%20I%20am%20not%20sure%20why.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20a%20linked%20sheet%2C%20I%20was%20using%20the%20cell%20values%20above%20but%20with%20each%20of%20the%20cells%20linked%20using%20%3A%3C%2FP%3E%3CP%3E%3D%2BIF(FINAL!V10%3D0%2C%22%22%2C(FINAL!V10))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20that%20it%20ignored%20the%20Zero's%20but%20all%20in%20all%20I%20think%20I%20might%20be%20just%20confusing%20the%20issue%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20the%20second%20sheet%20so%20that%20I%20can%20subdivide%20the%20set%20by%20filter%20again%20but%20maintain%20sight%20of%20the%20larger%20filtered%20value.%20-%20Its%20to%20do%20with%20price-earnings%20of%20shares.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1538812%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1539091%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20Help%20!!%20Median%20Range%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1539091%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F299316%22%20target%3D%22_blank%22%3E%40PETEMAGS%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThree%20solutions%20in%20the%20attachment.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1539437%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20Help%20!!%20Median%20Range%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1539437%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3BHi%20Detlef%20-%20Thank%20you%20so%20much%20for%20replying%20-%20is%20it%20possible%20to%20apply%20this%20formula%20in%20the%20cell%20above%20colum%20as%20in%20my%20sheet%20'Final%20V1'%20and%20related%20to%20data%20in%20sheet%20V9%3Av300%20-%20this%20is%20as%20there%20are%20multiple%20colums%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1539549%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20Help%20!!%20Median%20Range%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1539549%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F299316%22%20target%3D%22_blank%22%3E%40PETEMAGS%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOf%20course.%20You%20have%20to%20adjust%20the%20cell%20references.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1539557%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20Help%20!!%20Median%20Range%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1539557%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DMEDIAN(FILTER(%5BV9%3AV2999%5D%2C(%5BV9%3AV2999%5D%26lt%3B%26gt%3B0)*(SUBTOTAL(102%2COFFSET%5B%5BV9%2CROW(V9%3AV2999%5D%2C%5BV9%3AV2999%5D%5D%2CSEQUENCE(COUNT(%5BV9%3AV2999%5D)%2C%2C1)%2C%2C%2C)))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20this%20but%20its%20not%20working%3C%2FP%3E%3CP%3E%26nbsp%3BWhere%20you%20have%20a%20header%20I%20begin%20at%20row%209%20and%20the%20header%20is%20dynamic%20as%20linked%20to%20a%20data%20source%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20help%20me%20solve%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1539685%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20Help%20!!%20Median%20Range%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1539685%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F299316%22%20target%3D%22_blank%22%3E%40PETEMAGS%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DMEDIAN(FILTER(%5BV9%3AV300%5D%2C(%5BV9%3AV3000%5D%26lt%3B%26gt%3B0)*%0A(SUBTOTAL(102%2COFFSET%5B%5BV8%2CSEQUENCE(COUNT(%5BV9%3AV300%5D)%2C%2C1)%2C%2C%2C)))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIt%20seems%20you%20mixed%20up%20two%20formulas.%20It's%20either%20ROW()%20or%20SEQUENCE()%20but%20not%20both.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1539753%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20Help%20!!%20Median%20Range%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1539753%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Detlef%20-%20thanks%20so%20much%20for%20assistance%20and%20reply%20-%20I%20tried%20this%20in%20cel%20ref%20attached%2C%20but%20am%20getting%20error.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1539825%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20Help%20!!%20Median%20Range%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1539825%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F299316%22%20target%3D%22_blank%22%3E%40PETEMAGS%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DMEDIAN(FILTER(V9%3AV300%2C(V9%3AV300%26lt%3B%26gt%3B0)*(SUBTOTAL(102%2COFFSET(V8%2CSEQUENZ(COUNT(V9%3AV300)%2C%2C1)%2C%2C%2C)))))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1539897%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20Help%20!!%20Median%20Range%20Formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1539897%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20you%20so%20much%20that%20seems%20to%20work%20great%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi - I am trying to get the median of a large number of values in a column range example rows (c.3000)- such that the formula ignores zero / errors or blanks. Additionally, I want to be able to apply a filter and that the result is just using the filtered visible dataset. I have used the formula below, but it is giving me a value I don't think is correct.  Any help would be really appreciated:

 

=MEDIAN(IF(SUBTOTAL(2,OFFSET(V9,ROW(V9:V2999)-ROW(V9),0)),V9:V2999))

 

Sometimes this gives value and sometimes a blank and I am not sure why.

 

In a linked sheet, I was using the cell values above but with each of the cells linked using :

=+IF(FINAL!V10=0,"",(FINAL!V10))

 

So that it ignored the Zero's but all in all I think I might be just confusing the issue

 

I need the second sheet so that I can subdivide the set by filter again but maintain sight of the larger filtered value. - Its to do with price-earnings of shares.

 

 

Thank you so much

8 Replies
Highlighted

@PETEMAGS 

Three solutions in the attachment.

 

Highlighted

@Detlef Lewin Hi Detlef - Thank you so much for replying - is it possible to apply this formula in the cell above colum as in my sheet 'Final V1' and related to data in sheet V9:v300 - this is as there are multiple colums?

Highlighted

@PETEMAGS 

Of course. You have to adjust the cell references.

 

Highlighted

@Detlef Lewin 

=MEDIAN(FILTER([V9:V2999],([V9:V2999]<>0)*(SUBTOTAL(102,OFFSET[[V9,ROW(V9:V2999],[V9:V2999]],SEQUENCE(COUNT([V9:V2999]),,1),,,)))))

 

I tried this but its not working

 Where you have a header I begin at row 9 and the header is dynamic as linked to a data source

 

Could you help me solve

Highlighted

@PETEMAGS 

=MEDIAN(FILTER([V9:V300],([V9:V3000]<>0)*
(SUBTOTAL(102,OFFSET[[V8,SEQUENCE(COUNT([V9:V300]),,1),,,)))))

It seems you mixed up two formulas. It's either ROW() or SEQUENCE() but not both.

 

Highlighted

@Detlef Lewin 

Hi Detlef - thanks so much for assistance and reply - I tried this in cel ref attached, but am getting error.

Highlighted

@PETEMAGS 

=MEDIAN(FILTER(V9:V300,(V9:V300<>0)*(SUBTOTAL(102,OFFSET(V8,SEQUENZ(COUNT(V9:V300),,1),,,)))))

 

Highlighted
Best Response confirmed by PETEMAGS (Occasional Contributor)
Solution

@Detlef Lewin 

 

Thanks you so much that seems to work great