EDIT: Obrigado pelo arquivo de teste. Os resultados são mostrados na tabela abaixo. A captura de tela mostra as primeiras 25 linhas, mas a tabela real inclui todos os dados no seu arquivo de teste.
Acolunaauxiliar("∆ Ativos", coluna E) é o valor absoluto da diferença entre os ativos da empresa a ser avaliada e todas as outras empresas. A célula H6 mantém os ativos da empresa de teste e pode ser facilmente alterada para apontar para qualquer empresa na lista. Neste exemplo, aponta para B4. A fórmula em E4 é:
=ABS($E4-$H$6)
E é preenchido para criar a coluna auxiliar. A fórmula em H4 é:
=MEDIAN(IF(RANK(E4:E55,E4:E55,1)<22,D4:D55))
Esta é uma fórmula de matriz e deve ser digitada com CTRL + Deslocar + Enter , em vez de apenas Enter .
Como um teste, calculei a mediana P / E manualmente em G4 selecionando 21 P / E de acordo com a classificação da coluna auxiliar na coluna F. Os resultados são os mesmos que os da fórmula, então os dois métodos ignoram as células onde o P / E é "NA". MAS, se essas células ficarem em branco, a mediana calculada muda porque o array interno contém zero onde estavam os NAs.
Como funciona: A função RANK () retorna uma matriz das classificações dos valores da coluna auxiliar em ordem crescente. O IF () retorna uma matriz dos 21 índices P / E correspondentes às empresas com ativos mais próximos da empresa que está sendo avaliada. (Observação: usei 21 porque presumo que a empresa que está sendo avaliada está na lista. Caso contrário, altere a fórmula de acordo.) Finalmente, MEDIAN () calcula o valor mediano da matriz de 21 valores de ativos.
Fiquei um pouco surpreso ao descobrir que o RANK () poderia manipular dois arrays como argumentos, mas não arrays calculados , ou seja, ABS ($ I $ 4: $ I $ 508- $ I $ nn). É por isso que a coluna auxiliar era necessária. Ainda mais bizarro - RANK () pode lidar com uma matriz calculada ONE , mas não duas. Isso merece um "C'mon man ..." com certeza.
Espero que isso ajude e boa sorte.