Skip to main content
Support is Offline
Today is our off day. We are taking some rest and will come back stronger tomorrow
Official support hours
Monday To Friday
From 09:00 To 17:30
  Friday, 03 November 2017
  0 Replies
  2.3K Visits
0
Votes
Undo
Hi guys,

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
There are no replies made for this post yet.