Excel - Máximo entre dos fechas que se incrementan

Por acá en busca de un poco de ayuda: Tengo esta fórmula matricial:
=MAX(SI(ENTERO('1D'!A1439:A1445)>A208;SI(ENTERO('1D'!A1439:A1445)<=A209;'1D'!C1439:C1445))); funciona según lo requerido; que es buscar en la hoja 1D el valor mas alto de un rango, de acuerdo a dos fechas especificadas en otra hoja.
En la hoja 1D están los registros diarios; fecha y valor. En la otra hoja -que llamaremos 1W-, están las fechas por periodos de 7 días, se trata de que la fórmula busque en la hoja 1 el valor más alto en el periodo de siete días, de acuerdo a la fecha inicial de la semana especificada en 1W y la fecha final de esa hoja. El problema se da porque necesito copiar la fórmula en las celdas que siguen hacia abajo, pero que se incremente la cantidad de días.

Ejemplo: si en la celda C2 copio la fórmula
=MAX(SI(ENTERO('1D'!A1439:A1445)>A208;SI(ENTERO('1D'!A1439:A1445)<=A209;'1D'!C1439:C1445))), al copiarla a C3 debería quedar así:
=MAX(SI(ENTERO('1D'!A1446:A1452)>A210;SI(ENTERO('1D'!A1446:A1452)<=A211;'1D'!C1446:C1452))).
Nótese que en la 2da fórmula ha subido de 1439 a 1446 y de 1445 a 1452, de A208 a A210 y de A209 a A211. Este cambio lo estoy haciendo manualmente cada vez que copio la fórmula, y me gustaría automatizarlo.
Me recomendaron esta formula matricial, pero funciona en algunos casos y en otros no:
​=MIN(SI(('1D'!$A$1:$A$2000>A1)*('1D'!$A$1:$A$2000<=A2);'1D'!$D$1:$D$2000)). Tambien hay que considerar que la formula funcione cuando en el rango en que va a buscar el valor, hayan celdas con valor nulo o 0, para que los obvie
Gracias de antemano por sus comentarios

• Cons01.zip(140,4 KB)

Hola, Adolfo:
No queda claro de tu ejemplo lo que haces, pero, estoy casi seguro, que con otra estructura sería muchísimo más fácil y no necesitarías de ninguna fórmula. Me refiero a una estructura de base de datos y los cálculos con Tablas dinámicas: https://www.youtube.com/playlist?list=PLF1OL9sNXkSyOJETR9ST8xjwjFj9WrkME

Gracias por la respuesta. No, no me sirve trabajar con tablas dinamicas

Después de estar devanando los sesos he llegado a esta solución.

Primero el problema esta mal planteado porque explicas lo que haces, mal porque no funciona, en lugar de que es lo que quieres conseguir.
Tu formula es esta que me ha costado entender, no que hace sino que quieres hacer:


Esta es la que realizado:


Y aquí sobra los condicionales y la formula que queda es esta:


¿Que hace esto?

Evaluamos la función COINCIDIR() a evaluar:


Busca la fecha 05/05/2019 en la tabla de la hoja 1D y da que esta en la posición 7

Ahora cambio la fórmula por su valor:


Y hago la resta:


Esta función CONCATENAR convierte en una cadena string:


Resultado


La Función INDIRECTO convier un texto en una referencia:


Y ya tienes una función que la réplicas en toda la columna y se ajusta a tu necesidad. La columna A de la hoja 1D debe tener todas las fechas desde el 28/04/2019 hasta el 16/04/2023, si falta un solo día la fórmula deja de funcionar.
Otra forma de conseguir lo mismo es con la fórmula en la columna F de la hoja 1D.
Y por supuesto usando las Tablas Dinámicas que pueden dar resumen por semana como explica Pepe en el post anterior.


Saludos.
\\//_

• Cons01-V2.zip(153,2 KB)