jueves, 12 de enero de 2012

La función de Excel PROMEDIO.SI.

Veremos hoy un par de formas de calcular el PROMEDIO de un rango de celda en Excel obviando alguno de los elementos que lo componen. En concreto esta cuestión me la han planteado ya varias veces,por lo que he decidio subir una explicación de como obtener un resultado válido.
La petición de ayuda dice:

...podrian ayudar con alguna formula para hallar el promedio de 4 notas pero no sumar la nota menor, ejemplo
14
20
21
16
Por favor alguna formula para hallar lo que les menciono lineas arriba...


El fin está claro, obtener el PROMEDIO de un rango sin tener en cuenta el menor de los valores.
Supongamos un rango A1:A4 con los valores del ejemplo propuesto:

La función de Excel PROMEDIO.SI.


Como vemos una de las posibilidades más sencillas para obtener la media aritmética, esto es el PROMEDIO, sólo de los valores más altos, obviando el menor de ellos; para nuestro ejemplo la media de 20, 21 y 16, sería aplicar la siguiente función:
=PROMEDIO.SI(A1:A4;"<>"&K.ESIMO.MENOR(A1:A4;1))
con la que conseguimos que sólo tenga en consideración aquellos valores del rango A1:A4 diferentes del menor valor de ese mismo rango.


Podemos observar que esta función es la manera más sencilla para alcanzar nuestra meta, pero existen otras formas, por ejemplo, empleando una función matricial (Ctrl+Mayusc+Enter):
{=PROMEDIO(SI.ERROR($A$1:$A$4*SI($A$1:$A$4=K.ESIMO.MENOR($A$1:$A$4;1);"";1);""))}

La función de Excel PROMEDIO.SI.


El paso a paso de esta matricial, se ve en la imagen, en el rango B1:B4; donde se ha construido celda a celda un rango sólo con los elementos que necesitamos, es decir, con todos los valores menos con el menor; para luego aplicar un PROMEDIO de ellos. Lo valores válidos se han obtenido evaluando cada uno de las celdas del rango, comprobando si es ese valor el menor del rango A1:A4.
En una única función matricial podemos realizar el mismo cálculo como veíamos en el párrafo anterior.

56 comentarios:

  1. Hola !!

    Me gusta esta entrada. Yo me preguntaba si existe alguna forma para calcular, aparte del promedio, la desviación estándar "condicionada". Es decir, de un rango de datos que cumplan ciertas condiciones.

    Un saludo !!

    ResponderEliminar
    Respuestas
    1. Hola Ricardo,
      si es posible, sólo hay que trabajar de manera análoga, obteniendo un rango condicionado.
      Puedes ver una explicación en
      http://excelforo.blogspot.com/2012/01/la-funcion-desvestp-sujeta-una.html
      Espero sea lo que buscabas.
      Slds

      Eliminar
    2. Exactamente eso es lo que necesitaba !! Gracias y un saludo !!!

      Eliminar
  2. Hola buenas atardes,

    es posible utilizar la formula promedio.si en el siguiente ejemplo:

    Tengo 12 rangos, los cuales estos 12 rangos pueden variar desde 0 a 4 ejemplo:

    1 - este puede ser 0,2,3,4
    4 - este puede ser 0,1,2,3
    2 - este puede ser 0,1,3,4
    1 - este puede ser 0,2,3,4
    0 - este puede ser 1,2,3,4
    3 - este puede ser 0,1,2,4
    1 - este puede ser 0,2,3,4
    4 - este puede ser 0,1,2,3
    0 - este puede ser 1,2,3,4
    1 - este puede ser 0,2,3,4
    4 - este puede ser 0,1,2,3
    1 - este puede ser 0,2,3,4

    lo que pretendo aqui es sacar el promedio de estos 12 rangos, pero cuando exista un " 0 " la formula promedio no cuente los rangos que se encuentren en " 0 ".

    Saludos y espero me haya explicado correctamente.

    ResponderEliminar
    Respuestas
    1. Hola, que tal!
      si, claro es posible.
      Supongamos el rango de 12 celdas en A1:A12, entonces en C1 para calcular el promedio sólo de los valores diferentes a cero insertamos la función:
      =PROMEDIO.SI(A1:A12;"<>0")
      Espero te sirva
      Slds

      Eliminar
  3. Que tal Ismael, soy Michael espero y me recuerdes con algunas preguntas anteriores, la formula la cual me indicaste me funciono correctamente.

    Agradezco tu apoyo y excelente foro.

    Saludos,

    ResponderEliminar
    Respuestas
    1. ;-)
      un gusto 'leerte' de nuevo por aquí.
      Espero estés bien.
      Cordiales saludos

      Eliminar
  4. Muy buenas, te pediría por favor me explicaras cómo hacer un cálculo que me trae loco: se trata de un promedio de valores que no forman un rango continuo, sino que están ubicados en celdas separadas entre si. Muchas gracias!

    ResponderEliminar
    Respuestas
    1. Hola Emilio,
      si es simplemente un PROMEDIO (esto es, si no está condicionado), simplemente aplica la función PROMEDIO sobre las diferentes celdas... por ejemplo:
      =PROMEDIO(F6;H13;E9)

      Si fuera un promedio condicionado sobre rangos de celdas discontinuos, habría que darle una vuelta.. pero probablemente habría que construir un función personalizada en VBA.

      Saludos

      Eliminar
  5. Hola! tengo una planilla de datos diarios muy grande (1971 a la fecha). Y necesito saber los PROMEDIOS de cada mes. Lo pensaba hacer igual que con AutoSuma, que cuando filtro me suma sólo lo que filtré (por ej =SUBTOTALES(9;P4:P25) ). Pero con promedio no me deja, siempre me promedia todo y nunca solo lo que filtro. ¿como puedo hacer? gracias ! ! !

    ResponderEliminar
    Respuestas
    1. Hola,
      para promediar sobre los datos filtrados debes aplicar
      =SUBTOTALES(101;….)

      aunque mi recomendación sería que aplicaras una Tabla dinámica, agrupando por el campo Fecha y Resumiendo los valores por Promedio...

      Saludos cordiales

      Eliminar
  6. hola, buenas tardes ismael romero.
    tengo una duda, me podrias ayudar con esto:
    esp ing mat bio tec geo asig ef art promedio
    5 6 7 8 9 10 8 9 8 7.7 (quiero saber que puedo hacer para que no me saque el promedio si alguna materia es menor a 6)

    ResponderEliminar
    Respuestas
    1. Hola Hector,
      puedes aplicar un condicional del tipo
      =SI(MIN(A3:I3)<6;"sin nota";PROMEDIO(A3:I3))
      suponiendo que en A3:I3 tienes todas las notas de las asignaturas.. de esta manera con que haya una nota inferior a 6, no aparecerá nota.

      Slds

      P.D.:es suficiente que plantees la pregunta una sola vez, no hace falta la plantees vía email y por comentarios.

      Eliminar
  7. Buenas noches estimados...
    Me preguntaba si alguien me podría ayudar con el tema de promedios matriciales.
    En mi caso necesitaría calcular el promedio entre cantidades de 12 columnas osea meses del año pero que sólo incluya los valores mayores al 80% de valor más alto incluyendo el valor máximo dentro del análisis
    Ejemplo
    Ene 100
    Feb 30
    Mar 50
    Abr 70
    May 80
    Jun 60
    Jul 40
    Ago 20
    Set 55
    Oct 60
    Nov 90
    Díc 100
    Donde el promedio sería entre enero mayo noviembre y diciembre 92.5
    Espero se pueda...

    ResponderEliminar
    Respuestas
    1. Hola Andrés,
      no hace falta emplear matriciales, basta emplear la función PROMEDIO.SI:
      =PROMEDIO.SI(B1:B12;">="&0,8*MAX(B1:B12))
      siendo B1:B12 el rango de valores
      Saludos

      Eliminar
    2. Hola Ismael.
      Hace tiempo me planteaste una solucion que hasta hoy la utilizo.
      Pero ocurre que necesito una condicionante mas ahora quiero que evalue el promedio sobre el mismo rango pero sobre el 2 maximo "90" y que omita los valores "100"
      Donde el promedio sería entre mayo y noviembre = 85
      Saludos

      Eliminar
    3. Hola,
      no termino de entender cuál es el condicionante para llegar a ese 85 que comentas.. pero para determinar el segundo valor más alto puedes emplear en la estructura de la fórmula la función K.ESIMO.MAYOR(B1:B12;2)
      Espero te oriente
      Saludos

      Eliminar
    4. Gracias por comentar. ya emplee esa formula "PROMEDIO.SI(B1:B12,">="&0.8*MAX(K.ESIMO.MAYOR(B1:B12,2)))" en la parte de criterio pero como hago algo parecido en la parte de rango ya que lo que no quiero es que me tome en el valor mas alto.
      En resumen quiero que analize el promedio sobre las 12 filas que determine el maximo y que lo excluya y analize el promedio sobre el resto osea desde el segundo valor en adelante y aplicar el criterio que ya me sugeriste en un primer momento
      Ejemplo
      Ene 100 - excluir
      Feb 30
      Mar 50
      Abr 70
      May 80 - considerado en el criterio
      Jun 60
      Jul 40
      Ago 20
      Set 55
      Oct 60
      Nov 90 - considerado en el criterio
      Díc 100 - excluir

      90+80 =170
      promedio de 170 = 85

      Eliminar
    5. Hola,
      entiendo que eliminamos el máximo, pero el 80% se toma sobre el total de los valores (incluido dicho máximo...), en ese caso:

      =PROMEDIO(SI(SI(SI(B1:B12<>MAX(B1:B12);B1:B12;0)>=0,8*K.ESIMO.MAYOR(B1:B12;2);SI(B1:B12<>MAX(B1:B12);B1:B12;0);0)<>0;SI(SI(B1:B12<>MAX(B1:B12);B1:B12;0)>=0,8*K.ESIMO.MAYOR(SI(B1:B12<>MAX(B1:B12);B1:B12;0);2);SI(B1:B12<>MAX(B1:B12);B1:B12;0);0)))
      ejecutada matricialmente, por supuesto.
      Saludos

      Eliminar
  8. Buenos días: En primer lugar enhorabuena por el blog. Necesito una fórmula que me calcule el promedio de los resultados que se obtienen en una competición por diversos participantes, descartando en el cálculo del promedio el 20% de los mayores resultados obtenidos en esa competición por cada participante.

    ResponderEliminar
    Respuestas
    1. Hola Carlos,
      bueno el asunto es algo más complejo que lo expuesto... ya que al tener que discriminar por cada participante habría que realizar cálculos auxiliares sobre cada uno de ellos...
      Sólo tengo una duda en el planteamiento cuando hablas del '20% de los mayores resultados' te refieres sobre el valor o sobre el conteo de registros?

      Subiré una explicación al blog de cómo calcular ese dato
      Un saludo

      Eliminar
  9. Buenas tardes y felicitaciones por el foro, por favor quisiera me puedas orientar y ayudar, necesito una formula para poder calcular el promedio de notas de un rango determinado de alumnos, pero sin tomar en cuenta la nota mas baja de cada alumno
    osea
    N-1 / N-2 / N-3 / N-4 Promedio ___________________________________________________
    Luis 10 15 14 16 ejemplo (calcular promedio sin nota 10)
    jose´ 11 12 09 08
    Carlos 05 07 12 16
    Miguel 11 12 15 09

    Gracias!!!!!

    ResponderEliminar
    Respuestas
    1. Hola Steve,
      por ejemplo suponiendo los datos de 'Luís' en el rango C1:F1
      =PROMEDIO.SI(C1:F1;">"&MIN(C1:F1))
      Saludos

      Eliminar
  10. Me podrian ayudar en sacar solos las notas de los estudiantes que obtuvieron desde 13 hasta 17 como pongo la formula en el exel

    ResponderEliminar
    Respuestas
    1. Hola Lia,
      entiendo te refieres a la nota media...
      si es así:
      =PROMEDIO.SI.CONJUNTO(notas;notas;">=13";notas;"<=17")
      Saludos

      Eliminar
  11. Hola, agradecería saber como podría sacar promedio de 7 notas por solo tomando las 5 más altas.

    Gracias.

    ResponderEliminar
    Respuestas
    1. Hola,
      podría ser:
      =PROMEDIO.SI(notas;">"&K.ESIMO.MAYOR(notas;5))
      Saludos

      Eliminar
    2. Este comentario ha sido eliminado por el autor.

      Eliminar
    3. Pero si tengo 4 notas por ejemplo 19 10 15 10 y solo quiero sus 3 mejores notas como haria ya que hay dos notas 10

      Eliminar
    4. Hola Danny,
      escribiré un post para explicarlo..
      pero te adelanto que habrá que emplear la fórmula K.ESIMO.MAYOR en su formato matricial.
      Slds

      Eliminar
  12. como puedo calcular el promedio de muchos datos eliminando los 5 datos mas bajos?

    ResponderEliminar
    Respuestas
    1. Hola Daniel,
      qué tal estás?, un placer saludarte igualmente.

      Una posible solución la tendrías en el comentario de arriba.
      =PROMEDIO.SI(notas;">"&K.ESIMO.MAYOR(notas;5))
      tendrías que encontrar una regla que te indique el total de valores y así descubrir, por complementarios, el número total..

      Un cordial saludo

      Eliminar
  13. Hola Ismael ojala aca si grabe tengo un problema Te explico he creado un registro de clientes para llenar varias datos ( los macros son pedazos de lo que pude ver el en inter) pero la funcion es que cuando escribes los datos así sea con minúsculas se pone en mayúscula ....OK
    Cuando llenas todo y no pones la fuente te sale un mensaje de error (FALTO COLOCAR LA FUENTE), pero se borra todo lo que llenaste y avanza el correlativo (este es el error) Solo debería salir el mensaje sin borrarse todo lo llenado y sin avanzar el correlativo.
    Ojala me puedas ayudar =( Gracias

    ResponderEliminar
    Respuestas
    1. Hola Kity,
      entiendo tienes un UserForm con ciertos TextBox que tras rellenarlos completan una tabla en la hoja de cálculo.
      Supongo, es difícil decir con el detalle que das, tendrás asociados a cada textbox unos procedimientos con eventos del tipo _Exit, y serán estos los que hacen saltar el mensaje y la acción de borrado...
      Revisa esa parte, seguramente esté ahí la acción de borrado de todos los textbox

      Siento no poder decirte algo más concreto.
      Saludos

      Eliminar
  14. Hola y antes que nada, gracias por cualquier ayuda que puedan darme.
    Necesito sacar el promedio entre la celda AN3 y el número más alto entre AO3 y AP3. Esto es porque el alumno ha hecho recuperación y ha aprobado, entonces quiero obviar la nota más baja, pero como es otro rango distinto, no me sale.
    Un saludo.

    ResponderEliminar
    Respuestas
    1. Hola,
      podría ser:
      =PROMEDIO(AN3;MAX(AO3:AP3))

      Un saludo

      Eliminar
  15. BUENAS TARDE AMIGO QUERIA UNA AYUDA CON ESTE PROMEDIO.
    Si existen las 3 notas (PC,EJ,OS) se saca el promedio caso contrario mostrar FN
    PC EJ OS
    7 13 10
    16 18

    COMO ES QU DEBO HACER

    ResponderEliminar
    Respuestas
    1. Hola Daniel,
      puedes aplicar un condicional del tipo:
      =SI(O(A1="";B1="";C1="");Nota FN;PROMEDIO(A1:C1))
      estando las tonas PC, EJ y OS en A1:C1, y siendo Nota FN la celda donde estuviera dicha nota.
      Saludos

      Eliminar
    2. Muy bien tu post me ayudas por favor, si tengo tres notas 7-8-9 y quiero encontrar la nota mas alta como seria la formula?

      Eliminar
    3. Hola,
      puedes usar
      =MAX(A1:A3)
      suponiendo en A1:A3 los valores 7, 8 y 9 indicados.

      Saludos

      Eliminar
  16. Tengo 5 celdas para introducir notas y quiero sacar el promedio entre ellas pero con la condición de que si entre la mayor nota y la menor hay una diferencia de 3 puntos o mas, se eliminen la mayor y la menor. Entre las notas que se introducen puede haber "0" y casillas en blanco, que no contarían para sacar el promedio.

    ResponderEliminar
    Respuestas
    1. Agradecería ayuda; es para calcular las notas de las oposiciones.

      Eliminar
    2. Hola Francisco,
      buenos días, espero te encuentres bien, un placer saludarte igualmente.
      Imagina un rango B3:G3 con notas (incluyendo ceros y celdas vacías).
      Para controlar la diferencia puedes emplear un condicional:
      =SI(max(B3:G3)-min(B3:G3)>=3;promedio1;promedio2)
      la fórmula de promedio1 sería la que elimina el mayor y menor, por ejemplo algo así:
      PROMEDIO.SI.CONJUNTO(B3:G3;B3:G3;">"&MIN(B3:G3);B3:G3;"<"&MAX(B3:G3))
      promedio2 sería un promedio 'normal' sin contar con el cero
      PROMEDIO.SI.CONJUNTO(B3:G3;B3:G3;"<>0")

      la función PROMEDIO de Excel no tiene en cuenta celdas vacías en el cálculo.

      Saludos

      Eliminar
  17. Hola:
    Muchas gracias. Voy a probarlo y te digo si me funciona y si me aclaro. El "0" sin contaría; no el espacio en blanco. ¿Tendré que combinar las 3 funciones?
    Gracias de nuevo.
    Saludos

    ResponderEliminar
  18. Hola, buenos días:
    Ya he conseguido dar con la solución a mi problema, gracias a la inestimable ayuda que me proporcionó Ismael.
    La primera parte queda igual, a saber:
    =SI(MAX(B2:F2)-MIN(B2:F2)>=3;K3;K2)
    Mientras que K3 y K2 (en su ejemplo promedio1 y promedio2), insertados en las celdas K3 y K2, serían:
    =(SUMA(B2:F3)-MAX(B2:F3)-MIN(B2:F3))/(CONTAR(B2:F3)-2)
    =SI.ERROR(PROMEDIO(B2:F2)
    No sé si esta forma de hacerlo es muy ordodoxa, pero he conseguido que también funcione en LibreOffice Calc, que por otro lado es gratis y el único programa que se puede utilizar en los centros públicos de la Comunidad Valenciana.
    Saludos nuevamente y gracias por la ayuda.

    ResponderEliminar
    Respuestas
    1. Me alegro te sirviera/orientara.. y pudieras emplearlo en Calc
      Saludos

      Eliminar
  19. Hola Isma,

    Como podría calcular el promedio pero solo de los valores mayores q una celda?

    Yo hago: promedio.si (A1:J1;">K1") pero no hay manera, me echas un cable?

    Gracias

    ResponderEliminar
    Respuestas
    1. Hola Omar,
      sería:
      =promedio.si (A1:J1;">"&K1)
      Un saludo
      Ismael Romero

      Eliminar
  20. Hola Disculpa,

    Muy util tu post, peor tengo una duda utilicé esta formula para calcular unos promedios, pero su alguien tiene por ejemplo:
    1 , 1 , 7 , el promedio que saca es 7, es decir, elimina todos los numeros iguales al numero menor. Necesito algo que me permita eliniar solo una de las notas mas bajas, en decir, en el ejemplo eliminar solo uno de los 1, para que el promedio quede en 4 y no en 7 como hace esta formula. Cualquier ayuda se agracede enormemente!

    ResponderEliminar
    Respuestas
    1. Hola Demian,
      la cuestión es que si tuvieras varios elementos repetidos: 1,1,1,7 siempre y en todo caso solo deseas eliminar uno de esos valores...
      En este caso tendrías que generar un columna auxiliar con un cálculo corrido:
      =O(A1>K.ESIMO.MENOR($A$1:$A$4;1);CONTAR.SI($A$1:A1;A1)>1)
      Luego promediaríamos solo sobre los valores con verdaderos

      Saludos

      Eliminar
  21. ME GUSTO LA EXPLICACIÓN.GRACIAS

    ResponderEliminar
  22. Hola Ismael, consulta
    Quiero sacar un promedio entre 3 celdas, pero una de ellas esta con falla #!DIV/0! como lo hago para que no la considere cuando tenga que promediar?

    Pdta: Daniel San Martín

    ResponderEliminar
    Respuestas
    1. Hola Daniel,
      de primera se me ocurre la siguiente función matricial (recuerda validarla presionando Ctrl+Mayusc+Enter):
      =PROMEDIO(SI(NO(ESERROR(C3:E3));(C3:E3)*NO(ESERROR(C3:E3))))
      suponiendo en C3:E3 los valores...

      Saludos

      Eliminar
  23. CÓDIGO APELLIDOS NOMBRES MATERIA NOTA
    09282001 Vargas Johnny Cálculo 4,5
    12111041 Márquez Juan Carlos Física 2
    14171005 Martínez Manuel Deportes 4
    09282001 Vargas Johnny Química 2,5
    14171005 Martínez Manuel Cálculo 4
    13022030 Rolón Jesús Cálculo 5
    02351044 Pérez Carmelo Deportes 4,5
    09282001 Vargas Johnny Física 3,5
    02351044 Pérez Carmelo Cálculo 4
    14171005 Martínez Manuel Química 3
    12111041 Márquez Juan Carlos Cálculo 3,5
    09282001 Vargas Johnny Deportes 3,5
    02351044 Pérez Carmelo Física 4
    12111041 Márquez Juan Carlos Química 2,5
    13022030 Rolón Jesús Deportes 3
    13022030 Rolón Jesús Física 2,5
    14171005 Martínez Manuel Física 4,5
    02351044 Pérez Carmelo Química 3,5
    13022030 Rolón Jesús Química 4
    12111041 Márquez Juan Carlos Deportes 4

    hola tengo estos datos y necesito hallar el estudiante con menor promedio

    ResponderEliminar
    Respuestas
    1. Hola,
      podría ser:
      =INDICE(A2:A21;COINCIDIR(MIN(E2:E21);E2:E21;0))

      pero ojo!!, si hubiera una nota mínima en diferentes alumnos, obtendrías SOLO la primera coincidencia.
      En tu ejemplo, el de menor nota (un 2) es el de código: 12111041

      Saludos

      Eliminar