Excel 2016: Exclude a specific cell from a range when calculating a median

[Deleted]

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

[#2 · Accepted solution]

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.

[#3]

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")

[#4]

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!

[#5]

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.

[#6]

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!

[#7]

Moritz,

the functions 1 to 13 only operate on plain cell references and NOT on arrays.