Excluyendo ceros en cálculos con Excel

De acuerdo a Wikipedia, el cero pertenece al conjunto de los números enteros. Sea lo que fuere, en ciertos cálculos, el cero puede ser bastante molesto, en especial si lo hacemos con Excel.

Por ejemplo, supongamos esta lista de números

Si queremos contar cuántos elementos hay en la lista, sin incluir los ceros, podemos hacerlo fácilmente usando la función CONTAR.SI. Definamos un nombre que contenga el rango de números

lista_num: =Hoja1!$A$1:$A$20

la siguiente fórmula nos da como resultado 20: =CONTAR(lista_num)

Si queremos contar la cantidad de números en la lista, sin considerar los ceros, usaremos la fórmula

=CONTAR.SI(lista_num;"<>0")

Esta fórmula da como resultado 17.

Los problemas empiezan cuando queremos encontrar el mínimo de la lista, lo que hacemos usando la función MIN. Esta fórmula da como resultado 0

=MIN(lista_num)

Si queremos encontrar el mínimo de la lista, sin incluir los ceros, usamos esta fórmula

=SI(MIN(lista_num)<>0;MIN(lista_num);K.ESIMO.MENOR(lista_num;CONTAR.SI(lista_num;"=0")+1))

Esta fórmula realiza el cálculo en dos etapas. Primero comprueba si el mínimo es cero. Si no hay ningún cero en la lista, usa la fórmula MIN(lista_num).
Si el mínimo es cero, pasa a usar la segunda parte de la función SI, la función K.ESIMO.MENOR combinado con CONTAR.SI.

La sintaxis de la función K.ESIMO.MENOR es: K.ESIMO.MENOR(matriz;k)
Donde matriz es el conjunto de datos y k determina a partir de qué valor se calculará el mínimo. En nuestra fórmula k es determinado por la fórmula CONTAR.SI(lista_num;"=0")+1 que da como resultado, en nuestro ejemplo, 4.

Un problema similar, simétrico diría, tenemos si nuestra lista está formada sólo por números negativos.
En ese caso, calcular el máximo de la lista (supongamos la misma lista, pero con valores negativos), daría 0. Para calcular el máximo, sin tomar en cuenta los ceros usamos una fórmula similar (al rango le hemos dado el nombre lista_neg)

=SI(MAX(lista_neg)<>0;MAX(lista_neg);K.ESIMO.MAYOR(lista_neg;CONTAR.SI(lista_neg;"=0")+1))

Otro problema con ceros es calcular promedios. Excel ignora las celdas vacías al calcular promedios con la función PROMEDIO. Pero los ceros son tenidos en cuenta. Por ejemplo, la fórmula =PROMEDIO(lista_num) da como resultado 39,25. Pero si calculamos el promedio sin incluir los ceros, obtenemos 46,176.

Para calcular promedios sin tomar en cuenta los ceros tenemos varias posibilidades:

=SUMA(lista_num)/CONTAR.SI(lista_num;"<>0")

El problema con esta fórmula es que toma en cuenta las celdas vacías. Para evitar que esto suceda podemos usar la fórmula

=SUMA(lista_num)/(CONTAR(lista_num)-CONTAR.SI(lista_num;0))

Otra alternativa es usar la función SUMAPRODUCTO de esta manera

=SUMA(lista_num)/SUMAPRODUCTO(--(lista_num<>0))

EL doble signo "--" tiene como función forzar el cálculo de la expresión lista_num<> y en su lugar se puede, sencillamente, multiplicar la expresión por 1

=SUMA(lista_num)/SUMAPRODUCTO((lista_num<>0)*1)

Technorati Tags: MS Excel