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 regarding SIC codes in column G and the regarding P/E ratios in column AB. My goal is to determine the value of each company by taking the median of all other companies with the same SIC code.
HOWEVER, my problem is, that the P/E ratio of the company under consideration is also used to get a value of itself (which is not good). Thus, what I need is to exclude the P/E ratio of the company which I want to get a value for.
It works pretty well with the following formula:
=MEDIAN(IF($G$4:$G$503=G4;$AB$4:$AB$503))
where G4:G503 are my SIC codes and G4 is the SIC code of my company under consideration. In AB4:AB503 you find the P/E ratios. However, I need a second criterion which excludes the P/E ratio of the company I'm looking at.
Any ideas?
Thank you very much and best wishes, Moritz
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.
6 Replies
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.
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.
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.
