Search code examples
excelexcel-formulamedian

Excel find the median of last 3 number?

For example, in the cell B3 : =MEDIAN(A1:A3), and that formule works all cells from B3 to Bend, but not work for B1 and B2, I tried write =IFERROR(MEDIAN(A-1,A1),(MEDIAN(A$1,A1)) but the excel not allow nagetive number.

And also why =MEDIAN("A"&"1:A3") not work? How can I split A and 3 ?

Solution

  • The formula below would work in B3, B2 and B1 as well as all cell below B3.

    =MEDIAN(INDIRECT(ADDRESS(MAX(ROW()-2,1),1)&":"&ADDRESS(ROW(),1)))

    It splits the "A from the 3", as you say, with the ADDRESS function and then converts the result of that to a cell reference using INDIRECT.