Forum Discussion
Excel 2016: Exclude a specific cell from a range when calculating a median
I'm trying to calculate a median of a range of cells if a certain condition is met. However, I want to exclude one value from a range of cells. I got all the S&P 500 company tickers in column C, the ...
Hi Moritz,
Perhaps something like
=IFERROR(MEDIAN(IF($G$4:$G$503=G4;IF(ROW(G4)<>ROW($AB$4:$AB$503);$AB$4:$AB$503))),"NA")
where NA is returned if no more such stickers in the range. Array formula.
Awesome! That works out ;)
I got another possibility in a different forum:
=MEDIAN(IF(($G$4:$G$503=G4)*($C$4:$C$503<>C4),$AB$4:$AB$503)) {Array Entered}That one also works! Thank you very much!
Hi Moritz,
Yes, that's better, i missed columns C and G in one-to-one relationships.
And from all these variants I like more the one which Detlef suggested.