Forum Discussion

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

20-07 SAMPLE JAPAN.xlsx1.7 MB

excel

Formulas and Functions

PETEMAGS
Jul 22, 2020
Detlef_Lewin

Thanks you so much that seems to work great 🙂

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

Silver Contributor

Jul 22, 2020

PETEMAGS

Of course. You have to adjust the cell references.

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
  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.
  - PETEMAGS
    Copper Contributor
    Jul 22, 2020
    Detlef_Lewin
    Hi Detlef - thanks so much for assistance and reply - I tried this in cel ref attached, but am getting error.