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?
No comments:
Post a Comment