martes, 7 de febrero de 2017

Cálculo de un Promedio Excluyendo Altos y Bajos

Ha sido decidir un título para este post, aunque la idea es muy clara.
Cómo calcular el promedio sobre un rango de celdas excluyendo los dos valores máximos y mínimos, pero en caso de que alguno de estos valores más bajos o más altos esté repetido, solo tener en cuenta uno de ellos.!!.

Planteemos un ejemplo. Disponemos de este listado de valores:
0 - 2 - 2 - 2 - 5 - 6 - 8 - 12 - 12 - 15
y deberíamos calcular la media sobre los valores:
2 - 5 - 6 - 8 - 12
esto es, media aritmética = 6,60


El asunto podría parecer vanal, pero después de dar varias vueltas al asunto no conseguí una única fórmula con la que obtener el resultado directo... y es que trabajar con los valores repetidos y quedarse solo con un único valor correspondiente al máximo o mínimo es complejo
Mi solución (a falta de otra mejor) es trabajar el promedio como el cociente de una suma entre en un conteo.

Cálculo de un Promedio Excluyendo Altos y Bajos


Para esto, mi primer cálculo corresponde a la suma de los valores estríctamente mayores y menores al segundo valor más alto y bajo del rango.
Estos valores se calculan (ver celdas D4 y D5) con las funciones:
segundo valor más alto: =K.ESIMO.MAYOR(A1:A10;2)
segundo valor más bajo: =K.ESIMO.MENOR(A1:A10;2)

Así pues en B13:
=SUMAR.SI.CONJUNTO(A1:A10;A1:A10;">"&K.ESIMO.MENOR(A1:A10;2);A1:A10;"<"&K.ESIMO.MAYOR(A1:A10;2))
notemos que aplicamos desigualdes estrictas, dejando fuera del cálculo los segundos valores.
De forma parecida realizamos el conteo de valores que cumplen las condiciones. En B14:
=CONTAR.SI.CONJUNTO(A1:A10;">"&K.ESIMO.MENOR(A1:A10;2);A1:A10;"<"&K.ESIMO.MAYOR(A1:A10;2))


Ahora necesitamos evaluar si procede o no incluir una única vez (en caso de repetición) el segundo valor más alto y más bajo.
Estudiamos el caso para el segundo valor por arriba. En la celda C13:
=SI(CONTAR.SI(A1:A10;K.ESIMO.MAYOR(A1:A10;2))=1;0;K.ESIMO.MAYOR(A1:A10;2))
que nos indica que si hay repetición para este segundo valor más alto nos devuelve dicho valor, en caso contrario (es decir, que solo aparezca una sola vez) devuelva un cero...
En nuestro ejemplo al estar repetido dos veces el valor 12, nos devuelve un 12.

Repetimos la idea para el valor por debajo, para incorporar el conteo. En la celda C14:
=SI(CONTAR.SI(A1:A10;K.ESIMO.MAYOR(A1:A10;2))=1;0;1)
obtendremos un 0 si solo aparece en nuestro listado una vez y un 1 si más.


Replicamos los cálculos, con sus ajustes, para los valores más bajos.
En D13:
=SI(CONTAR.SI(A1:A10;K.ESIMO.MENOR(A1:A10;2))=1;0;K.ESIMO.MENOR(A1:A10;2))

y en D14 para el conteo:
=SI(CONTAR.SI(A1:A10;K.ESIMO.MENOR(A1:A10;2))=1;0;1)


Sumamos por separado cantidades del rango B13:D13 (para el numerador de nuestro cociente) y por otra parte sumamos el rango B14:D14 (para el denominador del cociente).

Finalmente dividimos una y otra cantidad consiguiendo nuestro promedio buscado.


Podríamos juntar todo en una sola celda, pero sería poco visual para el aprendizaje...

Una comprobación de lo correcto del proceso sería para el listado de valores siguiente:
0 - 2 - 4 - 5 - 5 - 6 - 8 - 12 - 14 - 15
y deberíamos calcular la media sobre los valores:
4 - 5 - 5 - 6 - 8 - 12
esto es, media aritmética = 6,66666

Nos fijamos que se excluyen del cálculo el 0 y 2 por debajo, y el 14 y 15 por arriba; y que al no estar repetidos dichos valores no se produce inclusión alguna de éstos en el proceso.

2 comentarios:

  1. Si solo dispusiéramos de una celda para hacer el cálculo, valdria
    =SUMAR.SI.CONJUNTO($A$1:$A$10;$A$1:$A$10;">"&K.ESIMO.MENOR($A$1:$A$10;2);$A$1:$A$10;"<"&K.ESIMO.MAYOR($A$1:$A$10;2))+(SI(CONTAR.SI($A$1:$A$10;K.ESIMO.MAYOR($A$1:$A$10;2))=1;0;K.ESIMO.MAYOR($A$1:$A$10;2)))+(SI(CONTAR.SI($A$1:$A$10;K.ESIMO.MENOR($A$1:$A$10;2))=1;0;K.ESIMO.MENOR($A$1:$A$10;2))))/((CONTAR.SI.CONJUNTO($A$1:$A$10;">"&K.ESIMO.MENOR($A$1:$A$10;2);$A$1:$A$10;"<"&K.ESIMO.MAYOR($A$1:$A$10;2)))+(SI(CONTAR.SI($A$1:$A$10;K.ESIMO.MAYOR($A$1:$A$10;2))=1;0;1))+(SI(CONTAR.SI($A$1:$A$10;K.ESIMO.MENOR($A$1:$A$10;2))=1;0;1)))

    Un saludo

    ResponderEliminar
    Respuestas
    1. Gracias Victor,
      claro, juntar todas las partes en una sola celda es válido...
      Didácticamente mejor por partes ;-)
      Sld

      Eliminar