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.
Moritz,
and without CSE keystroke.
=IFERROR(AGGREGATE(19,6,$AB$4:$AB$9/($G$4:$G$9=G4)/($C$4:$C$9<>C4),2),"NA")
Detlev, took me a while to test & understand that one.
One question remains though: Why are things like the median (12) and average (1) not working? And why do I need to take the second Quantile.Inc (19) - which is actually the median?
Thanks for your help!Moritz,
the functions 1 to 13 only operate on plain cell references and NOT on arrays.
