Pregunta Calcule la mediana de los 20 pares que están más cerca en términos de activos totales


Estoy tratando de calcular la relación P / E mediana de las 20 empresas más cercanas a la empresa para que se valore en términos de activos totales.

Obtuve todos los tickers de mi empresa (S & P 500) en la columna C, los activos totales respectivos en la columna I, y las relaciones P / E en la columna Z.

Obtuve esta fórmula, pero falta la condición de que solo quiero que las 20 compañías más cercanas sean consideradas para la mediana.

=MEDIAN(IF(($I$4:$I$508= |||the 20 closest companies or +/-10% of the value|||| )*($B$4:$B$508<>$B4);$Z$4:$Z$508))

Si eso no es posible en Excel, voy a buscar un rango de valores, donde los activos totales pueden ser como +/- 10% en comparación con la empresa que va a valorar.

¿Algunas ideas?


0


origen




Respuestas:


EDITAR: Gracias por el archivo de prueba. Los resultados se muestran en la tabla de abajo. La captura de pantalla muestra las primeras 25 filas, pero la tabla actual incluye todos los datos en su archivo de prueba.

enter image description here

La columna de ayuda ("Δ Assets", Columna E) es el valor absoluto de la diferencia entre los activos de la empresa que se va a valorar y todas las demás compañías. La celda H6 contiene los activos de la empresa de prueba y puede cambiarse fácilmente para que apunte a cualquier compañía de la lista. En este ejemplo, apunta a B4. La fórmula en E4 es:

=ABS($E4-$H$6)

Y se completa para crear la columna de ayuda. La fórmula en H4 es:

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

Esta es una fórmula de matriz y debe ingresarse con CTRL+Cambio+Entrar, en lugar de solo Entrar.

Como comprobación, calculé manualmente el P / E medio en G4 seleccionando los 21 P / E de acuerdo con el rango de la columna auxiliar en la columna F. Los resultados son los mismos que con la fórmula, por lo que ambos métodos ignoran las celdas donde el P / E es "NA". PERO, si estas celdas se ponen en blanco, la mediana calculada cambia porque la matriz interna contiene cero donde estaban las NA.

Cómo funciona: la función RANK () devuelve una matriz de los rangos de los valores de la columna auxiliar en orden ascendente. El IF () devuelve una matriz de las 21 relaciones P / E correspondientes a las empresas con activos más cercanos a la empresa que se valora. (Nota: utilicé 21 porque supongo que la empresa que se va a valorar está en la lista. De lo contrario, cambie la fórmula en consecuencia.) Finalmente, MEDIAN () calcula el valor mediano de la matriz de 21 valores de activos.

Me sorprendió un poco descubrir que RANK () podría manejar dos arrays como argumentos, pero no calculado matrices, es decir ABS ($ I $ 4: $ I $ 508- $ I $ nn). Esta es la razón por la columna de ayuda fue necesaria. Aún más extraño: RANK () puede manejar UNO matriz calculada, pero no dos. Esto merece un "C'mon man ..." seguro.

Espero que esto ayude y buena suerte.


1



Desafortunadamente eso no funciona. ¡Obtengo el #VALOR! error ¿Su solución significaría que necesitaba una columna de ayuda para cada empresa que quiero valorar? Eso sería bastante incómodo, ¿no? - Moritz
No, solo una columna auxiliar. Probé esto y funciona correctamente en los datos de muestra que simulé. ¿Puedes publicar (o compartir de otra manera) una muestra real de tus datos? 25 filas más o menos, incluidas al menos las Columnas I y Z. Y la ubicación de los activos de la compañía de prueba. Una captura de pantalla estaría bien. Edite su pregunta y agregue una si puede. Si no puede, agregue un enlace a la imagen, y lo insertaré. - Bandersnatch
Hola, cargué un archivo de prueba con ~ 50 compañías aquí: we.tl/D227kzB7Bs Esperando su solución;) - Moritz


Tengo una pregunta más. Ahora quiero combinar TA y ROE. El objetivo es encontrar las 5 compañías más cercanas a la compañía objetivo en términos de TA y luego las 3 compañías de las 5 que están más cercanas en términos de ROE.

¿Alguna idea de cómo resolver ese problema?

Archivo de prueba disponible aquí: https://we.tl/rNIrgWL9h8

¡Muchas gracias por ayudar chicos!

@Nayrb


0





Mientras Rank no acepta matrices calculadas (una sorpresa para mí también), Small hace:

=MEDIAN(IF(IF($C$4:$C$508=C4,"",ABS(I4-$I$4:$I$508))<=SMALL(IF($C$4:$C$508=C4,"",ABS(I4-$I$4:$I$508)),20),$Z$4:$Z$508)) {Array Entered}

La instrucción IF interna excluye a la empresa objetivo. De lo contrario, funciona de manera casi idéntica a la solución de Bandersnatch.


0



¿Te importaría publicar tu solución en Excel? Todavía tengo un #VALOR! - Moritz
¿Tienes algún #VALOR? errores en cualquiera de las celdas a las que hace referencia la fórmula? O valores de texto en su columna de activos? Cualquiera de esos hará que la fórmula falle. - Nayrb
Si, lo tengo. Obtuve algunas "NA" donde no hay un valor de activo total para una compañía específica. Si los elimino, ¡está funcionando! Muchas gracias! - Moritz
¿tienes alguna idea para mi otra pregunta? ¿Combinando dos criterios progresivamente?: / - Moritz