I got a test file with 10 companies. I want to calculate the median by combining 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! I already have the formula for calculating the median of the 3 companies closest to the firm to be valued in terms of TA and ROE, respectively (see columns I and J).
e.g. for the 3 (=N3) companies next to the target in terms of TA: {=MEDIAN(IF(IF($B$4:$B$15=B4;"";ABS(E4-$E$4:$E$15))<=SMALL(IF($B$4:$B$15=B4;"";ABS(E4-$E$4:$E$15));$N$3);$G$4:$G$15;""))}
Any idea how to solve that problem?
Test file available here: https://we.tl/rNIrgWL9h8
Thank you so much for helping out guys!