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

EDIT: Thanks for the test file. The results are shown in the table below. The screenshot shows the first 25 rows, but the actual table includes all the data in your test file.

The helper column ("∆ Assets", Column E) is the absolute value of the difference between the assets of the company to be valued, and all the other companies. Cell H6 holds the test company assets, and can easily be changed to point to any company in the list. In this example, it points to B4. The formula in E4 is:

=ABS($E4-$H$6)

And it is filled down to create the helper column. The formula in H4 is:

=MEDIAN(IF(RANK(E4:E55,E4:E55,1)<22,D4:D55))

This is an array formula and must be entered with CTRL+Shift+Enter, rather than just Enter.

As a check, I calculated the median P/E manually in G4 by selecting the 21 P/E's according to the rank of the helper column in Column F. The results are the same as using the formula, so both methods ignore the cells where the P/E is "NA". BUT, if these cells are made blank, the calculated median changes because the inner array contains zero where the NA's were.

How it works: The RANK() function returns an array of the ranks of the values of the helper column in ascending order. The IF() returns an array of the 21 P/E ratios corresponding to the companies with assets nearest to the company being valued. (Note: I used 21 because I assume the company being valued is in the list. If not, change the formula accordingly.) Finally, MEDIAN() calculates the median value of the array of 21 asset values.

I was a bit surprised to find that RANK() could handle two arrays as arguments, but not calculated arrays, i.e ABS($I$4:$I$508-$I$nn). This is why the helper column was necessary. Even more bizarre - RANK() can handle ONE calculated array, but not two. This deserves a "C'mon man..." for sure.

Hope this helps and good luck.