Excel 2016: Calculate the median of the 20 peers that are closest in terms of total assets

[Moritz Müller]

similar question than last time. I'm trying to calculate the median P/E ratio of the 20 firms that are closest to the firm to be valued in terms of total assets.

I got all my company (S&P 500) tickers in column C, the respective total assets in column I, and the P/E ratios in column Z.

I got this formula but there's the condition missing that I just want to have the closest 20 companies to be considered for the median.

=MEDIAN(IF(($I$4:$I$508= |||the 20 closest companies or +/-10% of the value|||| )*($B$4:$B$508<>$B4);$Z$4:$Z$508))

If that's not possible in Excel, I'm going for a range of values, where the totals assets can be like +/- 10% compared to the firm to be valued.

Any ideas?

Thank you very much and best wishes, Moritz

[#2]

Moritz,

=AGGREGATE(17,6,$Z$4:$Z$20/($B$4:$B$20<>B4)/($I$4:$I$20>=I4*0.9)/($I$4:$I$20<=I4*1.1),2)

[#3]

Thank you Detlev. I think I got the formula AGGREGATE. However, is it not possible to take the closest 20 firms or the closest 2% of firms (if one needs a relative value)?

[#4]

Moritz,

I played around and this could be a solution for the closest 20 firms.

{=AGGREGATE(17,6,$Z$4:$Z$500/($B$4:$B$500<>B4)/MMULT(--(ABS($I$4:$I$500-I4)=SMALL(ABS($I$4:$I$500-I4),TRANSPOSE(ROW($1:$500)))),ROW($1:$500)^0),2)}

[#5]

I'm afraid I get an error for that formula :\ #NUM! see my screenshot

The problem is that the 20 closest companies can lie above and below the total assets value of the company to be valued ... Would probably be easier for values just above or below that value.

[#6]

Sorry, I forgot to mention that this formula requires CTRL-SHIFT-ENTER.

[#7]

Of course, I've also tried that method. Still the #NUM! error...

[#8]

Moritz,

without access to your workbook I have no clue.

[#9]

Oh sorry, sure. I uploaded a test file with ~50 companies here: we.tl/D227kzB7Bs Looking forward to your solution! 

EDIT: I think the problem might be that I got some "NA"s in my dataset where there is no value for total assets. I keep trying.

[#10]

Moritz,

{=AGGREGATE(17,6,$Z$4:$Z$20/($B$4:$B$20<>B4)/MMULT(--(IFERROR(ABS($I$4:$I$20-I4),"")=AGGREGATE(15,6,ABS($I$4:$I$20-I4),TRANSPOSE(ROW($1:$5)))),ROW($1:$5)^0),2)}

I changed SMALL() to AGGREGATE() and added IFERROR().

[#11]

Thank you!

[#12]

Detlef, I'm impressed. Looks great.

[#13]

Got one more question. Now I want to combine TA and ROE. The objective is to find the 5 companies closest to the target company in terms of TA and then the 3 companies from those 5 which are closest in terms of ROE!

Any idea how to solve that problem?

Test file available here: https://we.tl/rNIrgWL9h8

Thank you so much for helping out guys!