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) ticke...
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)}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.
