Excel 2016: Calcule a mediana de um grupo de pares combinando duas medidas

Question

Eu obtive um arquivo de teste com 10 empresas. Eu quero calcular a mediana combinando TA e ROE. O objetivo é encontrar as 5 empresas mais próximas da empresa alvo em termos de AT e, em seguida, as 3 empresas daquelas 5 que estão mais próximas em termos de ROE! Eu já tenho a fórmula para calcular a mediana das 3 empresas mais próximas da empresa a serem avaliadas em termos de TA e ROE, respectivamente (ver colunas I e J).

por exemplo. para as 3 (= N3) empresas próximas ao alvo em termos de TA: {= MEDIANO (SE (SE ($ B $ 4: $ B $ 15 = B4; ""; ABS (E4- $ E $ 4: $ E $ 15) ) < = PEQUENO (SE ($ B $ 4: $ B $ 15 = B4; ""; ABS (E4- $ E $ 4: $ E $ 15)); $ N $ 3); $ G $ 4: $ G $ 15; "" ))}

Alguma ideia de como resolver esse problema? Arquivo de teste disponível aqui: link Muito obrigado por ajudar vocês!

    

Answer 1

Se você quiser fazer isso com uma única fórmula, ela se tornará um monstro ... mas você pode usar essa fórmula de matriz em L4 copiado para baixo

=MEDIAN(IF($B$4:$B$15<>B4,IF(ABS(E4-$E$4:$E$15)<=SMALL(IF($B$4:$B$15<>B4,ABS(E4-$E$4:$E$15)),5),IF(ABS(F4-$F$4:$F$15)<=SMALL(IF($B$4:$B$15<>B4,IF(ABS(E4-$E$4:$E$15)<=SMALL(IF($B$4:$B$15<>B4,ABS(E4-$E$4:$E$15)),5),ABS(F4-$F$4:$F$15))),3),$G$4:$G$15))))

Note que, como com suas fórmulas, se você calcular o 3 mais próximo desta forma (comparando com o 3º maior valor), então é possível que você inclua 4 ou mais quando houver empates no 3º menor número.