Obtener la duración de una serie de datos en Excel

Si, por ejemplo, la serie de temperaturas está en B1:B30, la siguiente fórmula devuelve el máximo de días seguidos en los que se han superado los 10 grados:

=MAX(FRECUENCIA(SI(B1:B30>10;FILA(B1:B30)-CONTAR.SI(DESREF(INDICE(B1:B30;1);;;FILA(INDIRECTO("1:"&FILAS(B1:B30))));">10"));SI(B1:B30>10;FILA(B1:B30)-CONTAR.SI(DESREF(INDICE(B1:B30;1);;;FILA(INDIRECTO("1:"&FILAS(B1:B30))));">10"))))

Se trata de una fórmula matricial, por lo que ha de ser introducida pulsando mayúsculas control entrada al mismo tiempo.

No me funciona. ¿Le puedo enviar la tabla?

A ver si es que no he entendido el problema. Este es el ejemplo con el que he estado trabajando para escribir la fórmula. En amarillo la serie de 7 filas seguidas con valores >10, que es lo que me devuelve la fórmula y creo que es correcto: http://jrgc.es/ejemplos/ejemplo_20150716a.xlsx

Pues sí está bien. Le ruego lo verifique en mi libro. Los datos  están en la hoja Datos globales y el resultado quiero hacerlo en la Hoja Cálculos al final en las celda E38 poniendo la temperatura condicionante en la celda C38

El libro lo puede descargar en https://drive.google.com/file/d/0B5akf6S8xevTZlFwY1d1TmdpT0U/view?usp=sharing 

=MAX(FRECUENCIA(SI('Datos globales'!B3:B1162>C38;FILA('Datos globales'!B3:B1162)-CONTAR.SI(DESREF(INDICE('Datos globales'!B3:B1162;1);;;FILA(INDIRECTO("1:"&FILAS('Datos globales'!B3:B1162))));">"&C38));SI('Datos globales'!B3:B1162>C38;FILA('Datos globales'!B3:B1162)-CONTAR.SI(DESREF(INDICE('Datos globales'!B3:B1162;1);;;FILA(INDIRECTO("1:"&FILAS('Datos globales'!B3:B1162))));">"&C38))))

en Cálculos!$E$38 parece que funciona. Devuelve 24, que por ejemplo valdría para 'Datos globales'!$B$54:$B$77

Recuerde que hay que introducirla pulsando mayúsculas control entrada al mismo tiempo.

Sí que funciona. Imagino que para hacer lo contrario, es decir para ver los días consecutivos en que no se ha llegado a una temperatura, solamente sería cambiar el signo mayor por menor ¿No? También quiero ver si, una vez obtenido el resultado, sería posible ver el intervalo de fechas en que ha ocurrido o al menos el mes y año.

Le estoy pidiendo demasiado ya. Gracias por su ayuda.

Sí, en principio la fórmula debería funcionar con cualquiera de los operadores de comparación de Excel ( >, >=, <, <=, =,  <>)

Usando la función MODA. VARIOS que hay disponible a partir de Excel 2010 (no sé si también está en la versión anterior) creo que es posible saber en qué fila comienza cada serie.

Por ejemplo, si en Cálculos! C38 ponemos 30, la fórmula en Cálculos! E38 devuelve 35. Ahora, si en cualquier celda ponemos la siguiente fórmula:

=FILAS(MODA.VARIOS(SI('Datos globales'!B3:B1162>C38;FILA('Datos globales'!B3:B1162)-CONTAR.SI(DESREF(INDICE('Datos globales'!B3:B1162;1);;;FILA(INDIRECTO("1:"&FILAS('Datos globales'!B3:B1162))));">"&C38))))

averiguaremos que hay 4 series de 35 temperaturas consecutivas >30.

Para saber en qué fila empieza la primera de esas 4 series podemos usar:

=COINCIDIR(INDICE(MODA.VARIOS(SI('Datos globales'!B3:B1162>C38;FILA('Datos globales'!B3:B1162)-CONTAR.SI(DESREF(INDICE('Datos globales'!B3:B1162;1);;;FILA(INDIRECTO("1:"&FILAS('Datos globales'!B3:B1162))));">"&C38)));1);SI('Datos globales'!B3:B1162>C38;FILA('Datos globales'!B3:B1162)-CONTAR.SI(DESREF(INDICE('Datos globales'!B3:B1162;1);;;FILA(INDIRECTO("1:"&FILAS('Datos globales'!B3:B1162))));">"&C38));0)+2

para las siguientes series, lo único que hay que hacer es cambiar ;1 por ;2 ;3 ;4

El +2 que hay al final se debe a que hay dos filas de títulos.

Todas estas fórmulas son, evidentemente, matriciales.

Genial. ¿y cómo hago para que me ponga la fecha de esa fila en la que empieza cada una de las series? Sería poner la celda correspondiente de esa fila de la columna fecha.

En el ejemplo que me envía de las 4 series de 35 días con temperatura mayor de 30, es correcto. Pero me da el mismo resultado para la serie 3 y 4, la fila 113 ¿Por qué?

=INDICE('Datos globales'!A:A;COINCIDIR(INDICE(MODA.VARIOS(SI('Datos globales'!B3:B1162>C38;FILA('Datos globales'!B3:B1162)-CONTAR.SI(DESREF(INDICE('Datos globales'!B3:B1162;1);;;FILA(INDIRECTO("1:"&FILAS('Datos globales'!B3:B1162))));">"&C38)));1);SI('Datos globales'!B3:B1162>C38;FILA('Datos globales'!B3:B1162)-CONTAR.SI(DESREF(INDICE('Datos globales'!B3:B1162;1);;;FILA(INDIRECTO("1:"&FILAS('Datos globales'!B3:B1162))));">"&C38));0)+2)

para la 1ª serie. Habrá que ponerle formato de fecha a la celda donde esté la fórmula, porque por defecto Excel le pondrá formato numérico.

No aconsejo usar muchas veces estas fórmulas (salvo lógicamente que no haya más remedio), porque tienen que crear varias matrices y operar con ellas unas cuantas veces, y al ser bastantes datos el rendimiento del libro al ser recalculado es muy probable que se resienta.

A mí me devuelve las siguientes fechas para las 4 series de 35 temperaturas > 30:
22/06/2012
28/07/2012
22/06/2013
03/08/2014

Pues a mí me coincide solamente la primera. Algo estoy haciendo mal. ¿Lo puede mirar en mi Libro?

 https://drive.google.com/file/d/0B5akf6S8xevTZlFwY1d1TmdpT0U/view?usp=sharing