Promedios con SUBTOTALES sin incluir ceros.

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

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

Cambios en el diseño del blog

Como seguramente habrán notado, he cambiado la plantilla del blog. Mi intención es lograr un diseño más claro, menos cargado, que el de la plantilla anterior.

En los próximos días seguiré haciendo cambios y agregando o quitando elementos. Ya que este blog está dirigido a sus lectores, vuestras opiniones, observaciones y sugerencias tienen una gran importancia para mí.

Están invitados a dejar vuestros comentarios en el formulario de comentarios que aparece al pie de la nota.

Technorati Tags: MS Excel

El cuaderno Personal.xls no aparece

El Personal.xls (en las versiones 97-2003 de Excel) o Personal.xlsb (en Excel 2007) es un cuaderno oculto que se activa cuando abrimos una sesión de Excel.

Toda rutina o función que guardemos en un módulo de este cuaderno estará disponible para todo cuaderno abierto en la misma sesión.

Ya hemos explicado que si el Personal.xls(b) no existe, podemos crearlo grabando una macro y guardándola en el este cuaderno

Este cuaderno está ubicado por defecto en la carpeta XLSTART. Para saber la ubicación exacta podemos usar la ventana Immediate del editor de Vba de esta manera



Como se puede ver en mi máquina la ubicación es

C:\Documents and Settings\locuser\Application Data\Microsoft\Excel\XLSTART

Todo cuaderno guardado en esta carpeta es activado al abrir una sesión de Excel.

Existen situaciones en las cuales hemos creado el Personal.xls(b) pero no es activado al abrir Excel. Esto puede deberse a muchas razones. Por ejemplo, puede haber sido borrado accidentalmente o haber sido movido a otra carpeta.

Muchas veces el remedio es volver a crear el cuaderno como hemos indicado más arriba. Pero si tampoco esto ayuda lo más probable es que el Personal.xls(b) haya sido incluido en la lista de elementos deshabilitados. Es decir, el cuaderno existe en la carpeta indicada pero Excel lo ha deshabilitado. Para “rehabilitarlo” usamos el menú Ayuda (puede aparecer también como “?”) – Acerca de Microsoft Excel

En la parte inferior de la ventanilla apretamos el botón Elementos deshabilitados



y en la lista de elementos deshabilitados



elegimos Personal.xls(b) y apretamos el botón Habilitar.

En Excel 2007 tenemos que apretar el botón de Office, luego el botón de Opciones de Excel, elegir la opción Complementos. En la parte inferior izquierda veremos la ventanilla Administrar, allí elegimos Elementos deshabilitados.

Technorati Tags: MS Excel