Forum Discussion

PETEMAGS's avatar
PETEMAGS
Copper Contributor
Jul 22, 2020
Solved

Please Help !! Median Range Formula

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...
excel
Formulas and Functions
  • PETEMAGS's avatar
    PETEMAGS
    Jul 22, 2020

    Detlef_Lewin 

     

    Thanks you so much that seems to work great 🙂

PETEMAGS's avatar
PETEMAGS
Copper Contributor
Jul 22, 2020

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?

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Jul 22, 2020

    PETEMAGS 

    Of course. You have to adjust the cell references.

     

    • PETEMAGS's avatar
      PETEMAGS
      Copper Contributor
      Jul 22, 2020

      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

      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor
        Jul 22, 2020

        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.

         

Resources