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
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
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
En la celda C12 ponemos la fórmula con la función SUBTOTALES, que al filtrar dará el resultado esperado
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: MS Excel
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
ResponderBorrarSaludos
CESAR MERA
No sería también factible? :
ResponderBorrar=SUMA(B2:B10)/CONTAR.SI(B2:B10;"<>0")
Utilizo ; como separador.
Antonio (Barcelona)
Antonio
ResponderBorrarno, 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.
me encantó la solución de las fórmulas! la de antonio también, muy buena
ResponderBorrarLa 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
ResponderBorrarJorge, un favor tengo este problema:
ResponderBorrar=(((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
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.
ResponderBorrarJorge, 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.
ResponderBorrarSon dos decimales los que me estaban haciendo que no me cuadrara el resultado del excel con el sistema contable como podrás apreciar.
Saludos
Creo que esta nota puede resutarte interesante.
ResponderBorrarSuper efectiva la propuestade Antonio, gracias por tu aporte resolvi el problema de promediar valores sin contar los ceros.
ResponderBorrarAntonio, 08 noviembre, 2009 01:27
No sería también factible? :
=SUMA(B2:B10)/CONTAR.SI(B2:B10;"<>0")
Utilizo ; como separador.
Antonio (Barcelona)