martes, 22 de marzo de 2016

Promedios con SUBTOTALES sin incluir ceros.

Un caso bastante frecuente es querer calcular el promedio de un rango de valores SIN incluir en el cálculo las celdas vacías o con valor cero (ver ejemplo).
En este ejemplo de hoy, además añadiremos un nuevo condicionante, queremos obtener ese promedio sin ceros al aplicar un filtro usando la función SUBTOTALES.


Comenzamos a trabajar desde nuestro rango de datos con un autofiltro incorporado:

Promedios con SUBTOTALES sin incluir ceros.



Para el correcto cálculo insertaremos en la celda B26 la siguiente fórmula matricial:
=PROMEDIO(SI(SUBTOTALES(102;DESREF(B3;FILA(B3:B24)-FILA(B3);;1));SI(B3:B24;B3:B24)))
recordemos validarla presionando Ctrl+mayusc+Enter!!!

Promedios con SUBTOTALES sin incluir ceros.



Pero ¿cuál es la explicación?.
La clave está en el uso de la función SUBTOTALES con su argumento 102 para forzar un cálculo de CONTAR valores numéricos:
SUBTOTALES(102;DESREF(B3;FILA(B3:B24)-FILA(B3);;1))
que nos devuelve, según los valores filtrado un rango virtual de 0 y 1, como podemos ver en la imagen siguiente:

Promedios con SUBTOTALES sin incluir ceros.


La causa de estos ceros y unos es su visibilidad.. y es que la función SUBTOTALES devuelve únicamente valor (uno) cuando el dato es visible!!, y cero cuando está oculto.
Necesario para distinguir qué datos están a la vista para operar sobre ellos.


Al incluir esta sucesión de ceros y unos en el condicional SI estamos discriminando todos aquellos valores que han quedado fuera del filtro.
=SI(SUBTOTALES(102;DESREF(B3;FILA(B3:B24)-FILA(B3);;1));SI(B3:B24;B3:B24))
Además de esto, con el argumento de verdadero del condicional:
SI(B3:B24;B3:B24)
discriminamos todos aquellos importes que sean cero:

Promedios con SUBTOTALES sin incluir ceros.



El resto es sencillo, ya que aplicamos la función PROMEDIO sobre los datos restantes... que no son otros que lo importes a la vista distintos de cero después de aplicar el filtro.

No hay comentarios:

Publicar un comentario

Nota: solo los miembros de este blog pueden publicar comentarios.