Please Help !! Median Range Formula

[PETEMAGS]

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

[#2]

@PETEMAGS 

Three solutions in the attachment.

[#3]

@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?

[#4]

@PETEMAGS 

Of course. You have to adjust the cell references.

[#5]

@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

[#6]

@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.

[#7]

@Detlef Lewin 

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

[#8]

@PETEMAGS 

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

[#9 · Accepted solution]

@Detlef Lewin 

Thanks you so much that seems to work great :)