Jul 22 2020 09:46 AM
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
Jul 22 2020 11:16 AM
Jul 22 2020 01:02 PM
@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?
Jul 22 2020 01:45 PM
Jul 22 2020 01:51 PM
=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
Jul 22 2020 02:45 PM
=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.
Jul 22 2020 03:18 PM
Hi Detlef - thanks so much for assistance and reply - I tried this in cel ref attached, but am getting error.
Jul 22 2020 04:02 PM
=MEDIAN(FILTER(V9:V300,(V9:V300<>0)*(SUBTOTAL(102,OFFSET(V8,SEQUENZ(COUNT(V9:V300),,1),,,)))))
Jul 22 2020 04:56 PM
SolutionJul 22 2020 04:56 PM
Solution