Salut baieti,
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.
Orice idei?
Thank you very much and best wishes,
Moritz
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.
Orice idei?
Thank you very much and best wishes,
Moritz