miércoles, noviembre 04, 2009

Promedios con SUBTOTALES sin incluir ceros.

Un lector me consulta si se puede usar la función SUBTOTALES para calcular un promedio sin tomar en cuenta los ceros.

Recordemos que la función SUBTOTALES tiene la propiedad de realizar cálculos sin tomar en cuenta valores ocultos al aplicar Autofiltro (o con Ocultar filas usando el argumento 109 en lugar de 9 para SUMA, 101 en lugar 1 para PROMEDIO, etc).

Supongamos esta tabla de dos columnas: Categorías y Valores



Excel promedios sin ceros

La fórmula en la celda B12 es

=SUBTOTALES(1,B2:B10)


donde el argumento “1” indica que queremos calcular el promedio.


Ahora filtramos la lista para dejar visible sólo los valores de la categoría C

Excel promedios sin ceros





Podemos ver que el resultado es 3.67. Si no tomamos en cuenta los ceros, el resultado tendría que ser 5.50 (aclaremos que debemos tener un buen motivo para no tomar en cuenta los ceros, a diferencia de celdas vacías que no son tomadas en cuenta por la función).

Una solución es crear una columna auxiliar con la fórmula =SI(B2<>0,B2,"") en la columna C



Excel promedios sin ceros

En la celda C12 ponemos la fórmula con la función SUBTOTALES, que al filtrar dará el resultado esperado


Excel promedios sin ceros

Si no queremos usar una columna auxiliar, podemos crear esta fórmula, adaptada de una propuesta por Bob Phillips

=SUMAPRODUCTO(SUBTOTALES(9,DESREF(B2,FILA($B$2:$B$10)-2,,1))*($B$2:$B$10<>0))/
SUMAPRODUCTO(SUBTOTALES(3,DESREF(B2,FILA($B$2:$B$10)-2,,1))*($B$2:$B$10<>0))




Technorati Tags:



10 comentarios:

  1. Hola Jorge, creo que una solución más fácil, en este caso es filtrar los valores de la columna B de manera que no incluya los ceros.... y listo, da 5.50

    Saludos


    CESAR MERA

    ResponderBorrar
  2. No sería también factible? :

    =SUMA(B2:B10)/CONTAR.SI(B2:B10;"<>0")

    Utilizo ; como separador.

    Antonio (Barcelona)

    ResponderBorrar
  3. Antonio
    no, porque sólo con SUBTOTALES puedes contar o sumar solamente las celdas visibles. Tu fórmula contaría y sumaría todas las celdas, independientemente si están vacías o no.

    ResponderBorrar
  4. me encantó la solución de las fórmulas! la de antonio también, muy buena

    ResponderBorrar
  5. La función de excel PROMEDIO.SI devuelve precisamente el promedio o media aritmética del total de las celdas que cumplan con una condición o criterio por nosotros definida. En 2007, PROMEDIO.SI resulta una valiosa alternativa al momento de calcular promedios específicos sobre volumenes de datos relativamente grandes. César MR

    ResponderBorrar
  6. Jorge, un favor tengo este problema:

    =(((4014.72/30)*14)*2709)*26
    El resultado de esta operación me da: 131,960,634.02
    Sin embargo requiero que cada vez que se realiza una operación redondee la cantidad de tal suerte que la sumatoria final sea de 131,956,690.32.
    Me puedes auxiliar??.......gracias

    ResponderBorrar
  7. No creo que pueda ayudarte. Tampoco entiendo la lógica de la operación. El resultado que obtienes es el correcto. Se puede redondear haacia arriba o hacia abajo, pero no producir un resultado arbitrario.

    ResponderBorrar
  8. Jorge, ya encontré la solución, de hecho me faltó decirte que no es un resultado arbitrario sino una cuestión de programación de decimales, la respuesta correcta es =redondear((4014.72/30)*14,2)*2709*26.
    Son dos decimales los que me estaban haciendo que no me cuadrara el resultado del excel con el sistema contable como podrás apreciar.
    Saludos

    ResponderBorrar
  9. Super efectiva la propuestade Antonio, gracias por tu aporte resolvi el problema de promediar valores sin contar los ceros.


    Antonio, 08 noviembre, 2009 01:27
    No sería también factible? :

    =SUMA(B2:B10)/CONTAR.SI(B2:B10;"<>0")

    Utilizo ; como separador.

    Antonio (Barcelona)

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.