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
Hola jorge:
ResponderBorrarPor 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.
No estoy seguro de haber entendido. ¿Se trata de colorear las celdas del rango evaluado(B7:B18 en nuestro ejemplo)?
ResponderBorrarA 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.
ResponderBorrarCálculos con los últimos n valores de una serie.
Atentamente: Winny
OK!,
ResponderBorrar1 - 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.
Hola Buenas noche Jorge..
ResponderBorrarvengo 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
Marco,
ResponderBorrarDESREF 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.
Hola Jorge!
ResponderBorrarDesde 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!
No estoy seguro de entender la consuta. ¿Estás tratando de crear una lista desplegable?
ResponderBorrarDisculpa, me explico,
ResponderBorrarSupongamos 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!
Quisiera saber como puedo sumar una serie de valores pero solo de los 3 ultimos meses introducidos pero no estas seguidos
ResponderBorrarCon SUMA.SI.CONJUNTO o con SUMAPRODUCTO, usando los meses como criterio.
ResponderBorrarBuenas noches, me gustaria saber la suma de los tres últimos meses que tengan datos,me gustaria mandarle el ejemplo, pero no sé como.
ResponderBorrarFijate aquí
ResponderBorrartengo un problema necesito sacar el promedio de las 3 ultimas fechas menores a la que coloco como parametro por ejemplo puedo tener una tabla con 30 filas si coloco como parametro la fecha (10-03-2017) y el producto A y la categoria B, teniendo en cuenta d que las 3 ultimas fechas de venta pueden estar en cualquier ubicacion de la tabla como podria hacer para sacar el promedio
ResponderBorrarEn los próximos días publicaré un post sobre el tema.
ResponderBorrarBuenas tardes; es posible con esta formula calcular el promedio de los ultimos 8 datos de una tabla sin incluir el ultimo?
ResponderBorrarBuenas tardes: es `posible con esta formula calcular el promedio de los ultimos 8 datos de una tabla sin incluir el ultimo dato?
ResponderBorrarEn forma similar, usando DESREF. El primer argumento de DESREF es la primer celda del rango de valores (supongamos B1); elk segundo es el total de filas del rango (que calculamos con CONTAR) menos 9 (8 valores más lel último que no hay que tomar en cuenta); el tercer argumento es 0, el cuarto 8 (en número de filas en el rango) y el último argumento de la función es 1.
ResponderBorrarSi nuestro rango empieza en B1 y tiene 30 filas, la fórmula será
=PROMEDIO(DESREF(B1,CONTAR(B1:B30)-9,0,8,1))