Saturday 1 September 2018

microsoft excel - Calculate the median of the 20 peers that are closest in terms of total assets

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

Where does Skype save my contact&#39;s avatars in Linux?

I'm using Skype on Linux. Where can I find images cached by skype of my contact's avatars? Answer I wanted to get those Skype avat...