Tom necesita promediar una serie de celdas no contiguas, excluyendo cualquier celda que pueda contener valores cero. Específicamente, necesita promediar las celdas A1, C1, E1, G1 y J1, pero solo contando aquellas celdas que no contienen cero.

Antes de entrar en lo que funciona, echemos un vistazo a lo que no funciona.

Primero que nada, no funciona simplemente sumar las celdas y dividir por 5; eso no tiene en cuenta los valores cero. En segundo lugar, no funciona usar CONTAR.SI en el denominador de su fórmula, como se muestra aquí:

=(A1+C1+E1+G1+J1) / COUNTIF(A1:J1,"<>0")

Esto no funciona porque examina y cuenta las celdas dentro del rango completo de A1: J1, no solo las cinco celdas que desea que se consideren en el promedio. También podría pensar que podría seleccionar sus cinco celdas no contiguas, darles un nombre y luego usar el nombre en su fórmula. Si bien Excel le permite crear el nombre, lo siguiente da un error:

=SUM(MyCells) / COUNTIF(MyCells,"<>0")

Parece que COUNTIF solo funcionará con un único rango contiguo, por lo que la naturaleza no contigua del rango MyCells hace que la función caiga en picada.

Puede calcular el promedio de estas cinco celdas aplicando un poco de «engaño» a su denominador, de esta manera:

=(A1+C1+E1+G1+J1) / ((A1<>0)+(C1<>0)+(E1<>0)+(G1<>0)+(J1<>0))

La evaluación realizada en cada celda del denominador devuelve un 1 (para Verdadero) o un 0 (para Falso) dependiendo de si la celda contiene un valor distinto de cero o no. Esta serie de valores se suma, proporcionando el recuento necesario de celdas distintas de cero para el denominador.

Tenga en cuenta que la discusión aquí ha sido todo sobre el denominador en la fórmula, no el numerador. La razón es simple: puede agregar los cinco valores al numerador; los valores cero allí realmente no importan.

El único lugar que importan es en el denominador, que es lo que hace que calcular este promedio sea tan complicado.

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (7842) se aplica a Microsoft Excel 97, 2000, 2002 y 2003. Puede encontrar una versión de este consejo para la interfaz de cinta de Excel (Excel 2007 y posterior) aquí:

link: / excelribbon-Averaging_a_Non-Contiguous_Range [Promedio de un rango no contiguo].