Cálculos con los últimos n valores de una serie en Excel

lunes, enero 09, 2012

Un compañero de trabajo quería calcular el promedio de los últimos 12 valores de una serie. En su caso, el precio promedio de una serie de productos



Para calcular el promedio de los últimos doce meses usaremos esta fórmula

=PROMEDIO(DESREF(B5,CONTAR(B5:B160)-12,0,12,1))



A medida que agreguemos (o quitemos) valores, la fórmula se ajustará automáticamente.

Sobre el funcionamiento de la función DESREF ya hemos escrito en este blog. El “truco” aquí es que usamos CONTAR para encontrar la última celda no vacía (suponemos que contienen números) y luego “retrocedemos” 12 puntos atrás.
En nuestro ejemplo

CONTAR(B5:B160)-12

da 2 (14-12) lo que nos “lleva” a B7; luego nos extendemos hasta B18 usando 12 como argumento en DESREF.

Un detalle a tomar en cuenta es que el rango de valores debe ser continuo (sin celdas vacías entre dos o más valores); en caso contrario el resultado será incorrecto.

Podemos modificar la fórmula de manera que la cantidad de valores a considerar en el cálculo se determine de forma dinámica



Usamos el valor en la celda B1 como argumento para determinar la cantidad de meses a tomar en cuenta

=PROMEDIO(DESREF(B5,CONTAR(B5:B160)-B1,0,B1,1))

Apéndice: en esta nota muestro como resaltar las celdas comprendidas en el cálculo con formato condicional

13 comments:

Anónimo,  10 enero, 2012 13:57  

Hola jorge:
Por favor como se puede hacer en formato condicional colorear las celdas de los números que se esta calculando los promedios indicados en el ejemplo.
Muchas gracias un placer saludarlo.

Jorge L. Dunkelman 10 enero, 2012 16:21  

No estoy seguro de haber entendido. ¿Se trata de colorear las celdas del rango evaluado(B7:B18 en nuestro ejemplo)?

Anónimo,  10 enero, 2012 17:17  

A sí es Jorge ¿Se trata de colorear las celdas o los números del rango evaluado(B7:B18 en su ejemplo)? que tiene como título.
Cálculos con los últimos n valores de una serie.
Atentamente: Winny

Jorge L. Dunkelman 10 enero, 2012 18:31  

OK!,
1 - Seleccionamos el rango a evaluar (B5:B160 en nuestro ejemplo)

2 - en formato condicional-nueva regla-fórmula ponemos ésta

Y(FILA(C5)>=CONTAR($C$5:$C$160)+4-$C$1+1,FILA(C5)<=CONTAR($C$5:$C$160)+4)

poner atención a los $ en la fórmula.

3 - elegimos el fondo de nuestro agreado

4 - Listo

Cómo funciona y otras alternativas las explicaré en una nota.

Marco Galeana,  15 enero, 2012 06:51  

Hola Buenas noche Jorge..
vengo siguiendo tu blog y no he encontrado conveniente molestarte para realizarte una pregunta ya que me hace muy explicito a lo que escribes.. pero en esta ocasión me surgió una duda....
en la funcion DESREF(B5,CONTAR(B5:B160)-12,0,12,1)y en referencia al link explicando la funcion DESREF me queda claro hasta este punto: -12,0..y vuelves a escribir 12... segun en tu link los ultimos 2 argumentos se refieren a "argumentos de rango" ...
la pregunta es: el # 12 (en este caso)se repite SIEMPRE dentro de los argumentos de celda como argumentos para rango .. y si es asi .. por que
o como lo definirias el numero 12 en este caso...
espero pues tu comentario...
saludos desde MX D.F

Jorge L. Dunkelman 15 enero, 2012 07:33  

Marco,
DESREF permite calcular dos tipos de rangos a partir de una celda determinada (el primer argumento de la función):

# - la dirección de una celda específica, definiendo con los dos siguientes argumentos a que distancia se encuentra de la celda del primer argumento (en nuestro ejemplo calculamos cuantas filas con CONTAR)

# - la dirección de un rango de celdas continuas, con el segundo par de argumentos,

Como explico en la nota,CONTAR(B5:B160)-12 calcula donde empieza el rango (12 celdas antes la última celda de la lista) y el segundo 12 nos lleva al final de la lista.

Anónimo,  25 julio, 2012 22:01  

Hola Jorge!

Desde que te leo utilizo la funcion DESREF y es espectacular. Pero me surje una duda:

No tengo ningun problema para hacer calculos entre rangos (del 1al5 o del 3al9) con DESREF, pero cuando tengo que coger un un rango para calcular en draw-down de una serie no se como hacerlo.

El valor me tiene que aparecer en una celda.

A ver si se te ocurre como hacerlo a ti...

Gracias!

Jorge L. Dunkelman 26 julio, 2012 10:16  

No estoy seguro de entender la consuta. ¿Estás tratando de crear una lista desplegable?

Anónimo,  26 julio, 2012 21:53  

Disculpa, me explico,

Supongamos que tenemos esto

Trades Close Total EqTop DD Rango
1 $0,00 10.000 10.000 0% 4
2 ($159,44) 9.841 10.000 -1,59% 7
3 $78,06 9.919 10.000 -0,81%
4 $90,56 10.009 10.009 0,00%
5 $90,56 10.100 10.100 0,00%
6 ($96,94) 10.003 10.100 -0,96%
7 $90,56 10.093 10.100 -0,06%
8 $103,06 10.196 10.196 0,00%

Estas serian las columnas para calcular el DrawDown, hay mas columnas en la tabla. Entonces, para saber el maximo DD pongo MIN(E:E) y ya sta.
Entonces, supongamos que quiero la rentabilidad ($) de un periodo. Hago SUMA(DESREF(B1;F2;0;F3-F2+1))
Pero lo complicado es saber el DD de un periodo en concreto, es decir, saber que caida he tenido del Trade 4 al 7 sin tener en cuenta los datos anteriores. Ya que si hago MIN(DESREF(E1;F2;0;F3-F2+1)) solo me dice el minimo que encuentra en ese periodo, pero no el de la serie seleccionada.
Necesito que recalcule el DD como si el inicio fuese el Trade 4 por ejemplo.

No se si ahora me habre explicado bien.

A ver si tu o puedes descifrar...

Gracias!

Anónimo,  11 marzo, 2014 00:17  

Quisiera saber como puedo sumar una serie de valores pero solo de los 3 ultimos meses introducidos pero no estas seguidos

Jorge Dunkelman 11 marzo, 2014 06:54  

Con SUMA.SI.CONJUNTO o con SUMAPRODUCTO, usando los meses como criterio.

francisco de la gala 11 marzo, 2014 23:58  

Buenas noches, me gustaria saber la suma de los tres últimos meses que tengan datos,me gustaria mandarle el ejemplo, pero no sé como.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP