Forum Discussion

SaraSmile's avatar
SaraSmile
Copper Contributor
Feb 02, 2019

Median

Pls help. I have a column of numbers that range 2-78 (2, 13, 62, 75, 15, 80). What is the formula to get the median of this column while excluding any numbers over 60? I’m going to keep adding to this column so I need the median formula to only include numbers 60 and below. Thank you.
  • Hi,

     

    You may use array formula (Ctrl+Shift+Enter to enter it)

    =MEDIAN(IF(A2:A78<=60,A2:A78,""))

    or regular one

    =AGGREGATE(16,6,A2:A78/(A2:A78<=60),0.5)
  • Hi,

     

    You may use array formula (Ctrl+Shift+Enter to enter it)

    =MEDIAN(IF(A2:A78<=60,A2:A78,""))

    or regular one

    =AGGREGATE(16,6,A2:A78/(A2:A78<=60),0.5)

Resources