Promedios con SUBTOTALES sin incluir ceros.

miércoles, noviembre 04, 2009

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 comments:

Colombiano 05 noviembre, 2009 08:16  

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

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)

Jorge L. Dunkelman 08 noviembre, 2009 17:57  

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.

Anónimo,  13 noviembre, 2009 16:38  

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

Anónimo,  11 marzo, 2010 15:08  

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

Anónimo,  05 noviembre, 2011 03:00  

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

Jorge L. Dunkelman 05 noviembre, 2011 08:34  

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.

Anónimo,  07 noviembre, 2011 19:29  

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

Jorge L. Dunkelman 07 noviembre, 2011 19:48  

Creo que esta nota puede resutarte interesante.

Anónimo,  26 junio, 2012 20:59  

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)

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP