Promedio Móvil de x Meses

"Tengo un histórico con la cifra de ventas mensual y me gustaría poder calcular el promedio de ventas de los últimos x meses hasta la fecha de hoy".

No problemo. Lo resolveremos haciendo uso de las funciones SI.ERROR, PROMEDIO, DESREF, COINCIDIR, BUSCARV y HOY. Supongamos que tenemos la siguiente tabla con la fecha y su cifra de ventas. Preparamos además la entrada de datos, es decir, el número variable de meses para calcular el promedio:

Empezamos calculando la fecha actual. Para ello nos ponemos en la celda C4 y escribimos la fórmula  =HOY()

Seleccionamos el rango E3:E32 y le damos el nombre Datomes (como siempre lo podemos crear haciendo clic en el Cuadro de nombres, a la izquierda de la barra de fórmulas, escribiendo dicho nombre y pulsando Enter). Nos situamos ahora en C6 y escribimos la fórmula:

=SI.ERROR(PROMEDIO(DESREF(F2;COINCIDIR(BUSCARV(C4;Datomes;1);

Datomes);;-C2;));"No disponible")

Con la función BUSCARV localizamos la fecha actual en nuestra tabla de ventas. Al anidarla dentro de la función COINCIDIR, obtendremos el número de fila en el que se encuentra dicha fecha. A su vez, COINCIDIR se encuentra anidada dentro de la función DESREF y es el argumento de fila, esto es, partiendo de la referencia de celda F2 tiene que contar tantas filas como devuelva COINCIDIR(BUSCARV(C4;Datomes;1). Ya tenemos la fila referente al mes corriente, ahora nos queda indicarle desde qué mes queremos realizar el cálculo del promedio, es decir, de los últimos 12 meses, de los últimos 6 meses, etcétera. Esto lo resolvemos con el argumento alto de la función DESREF. En concreto tendrá que retroceder desde la fila de la fecha actual hasta el número de meses que le indiquemos en la celda de entrada C2, y por ello le ponemos signo negativo a dicha referencia (-C2).

Le he añadido la función SI.ERROR para que si indicamos un número de meses demasiado elevado no aparezca el error #¡REF! sino que aparezca un texto un poco más estético del tipo "No disponible":

Se me olvidaba... Para conseguir que el texto del rótulo de la media(B6) sea variable, es decir, que cambie en función del número de meses que escribamos en C2, tenemos que poner la siguiente fórmula en B6:  ="Media "&C2&" meses:"

Cálculo del NPS (Net Promoter Score)

"¿Podrías indicarme cómo calcular el Net Promoter Score (NPS) con excel?"

Curiosamente he recibido varios mails en las últimas semanas preguntándome diversas cuestiones sobre esta herramienta, el NPS. Aunque la mayoría quieren saber cómo se halla con excel, permitidme una pequeña introducción para aquellos que no sabéis de qué estamos hablando. El NPS es un indicador para medir la satisfacción del cliente en términos de si recomienda tu producto (promotor), le es indiferente (pasivo) o le disgusta tu producto (detractor). La idea es de Fred Reichheld y data de 2003. El modelo es muy sencillo: se les pregunta a los clientes si recomendarían tu producto/servicio o no y que lo valoren de 0 a 10, siendo el cero que no lo recomendarían en ningún caso y siendo el 10 que lo recomendarían seguro. Los valores 9 y 10 son los denominados promotores; los valores 7 y 8 son pasivos; y los valores por debajo de 7 son los detractores. La fórmula que se aplica al total de encuestas realizadas es la siguiente:

NPS= %Promotores - %Detractores

 

Vamos a calcular el NPS haciendo uso de las funciones CONTAR.SI y CONTAR partiendo del siguiente ejemplo:

Seleccionamos el rango C3:C22 y le damos el nombre PUNTOS. A continuación, nos situamos en F2 y escribimos la fórmula:

Indicar finalmente que Cualquier puntuación del NPS que supere el 0% se considera como buena, ya que cuando esto sucede significa que el número de personas que han dado puntuaciones de 9 ó 10 es superior al número de personas que han dado el resto de puntuaciones. A partir del 50% se considera un resultado excelente.