Forum Discussion
Excel 2016: Calculate the median of the 20 peers that are closest in terms of total assets
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
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)
- 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)?
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)}
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!
