Fórmula para devolver el valor de una columna cuando la condición se cumple por primera vez en otra

Question

Ejemplo:

Dada una hoja de cálculo con cuatro columnas: A, B, C y D, y la fila 1 es una fila de encabezado...

A2=1
B2=(Entero positivo aleatorio)
C2=(Otro entero positivo aleatorio)

A3=A2+1
B3=B2-C2
A4=A3+1
B4=B3-C2

...

D2 es la celda problemática. En D2, quiero devolver el valor de la columna A que coincida con la primera instancia de un valor en la columna B que sea <=0.

Answer 1

De manera similar a la respuesta de Paul, usaría IndexyMatch

=INDEX($A:$A,MATCH(0,$B:$B,-1)+1)

1. Utilice columnas completas para que no sea necesario ajustarlas a la cantidad de datos de la columna.
2. Usar Indexen lugar de Offsetdesde Offsetes volátil y Indexno lo es. (Las funciones volátiles se recalculan en cada cálculo de hoja, mientras que las funciones no volátiles se recalculan solo cuando cambian los datos a los que se hace referencia. Demasiadas funciones volátiles pueden ralentizar Excel).

Answer 2

Puede utilizar MATCH para obtener lo que necesita.

MATCH(0,B2:B6,-1)+1Buscará B2:B6el primer valor que sea igual o menor que el número 0y devolverá su posición relativa. Está indexado desde cero, por lo que agregamos uno para que coincida con su índice.

Indexado desde cero significa que la fila B2 es 0, B3 es 1, B4 es 2 y B5 es 3. Entonces MATCH devolverá "3".

Tiene su propio índice en la columna A, y si no fuera secuencial, contuviera otros valores o no comenzara desde 1, podría usar offset para llegar a él:

=OFFSET(A2,MATCH(0,B2:B6,-1),0)

Entonces, esto es usar el resultado de MATCH para hacer una cuenta regresiva desde A2 para encontrar el valor en esa posición de fila.

Answer 3

¿No podrías calcular eso usando solo B2 y C2?

=CEILING(B2/C2,1)+1

Según mi comentario en la respuesta de Chris:Versión actualizada

=INDEX($A:$A,MATCH(TRUE,INDEX($B:$B<=0,0),0))

Esto debería evitar el problema inherente a la sugerencia de Chris de que se obtiene el valor incorrecto cuando el primer valor <=0 es 0.

La parte $B:$B<0devuelve una "matriz" de TRUE/FALSEvalores, el primero TRUEobviamente coincide con el valor de la primera columna B <0, MATCH luego encuentra la posición de esa primera instancia e INDEX luego obtiene el valor correspondiente de la columna A.

El segundo ÍNDICE sólo está ahí para evitar la "entrada en la matriz"; funciona sin eso, es decir

=INDEX($A:$A,MATCH(TRUE,$B:$B<=0,0))

.......pero esa versión necesitaría ser "ingresada en la matriz", es decir, confirmada con CTRL+SHIFT+ENTER.

Es un poco más ineficiente que las sugerencias anteriores, usar toda la columna (y eso no funciona en Excel 2003 o versiones anteriores; en esas versiones es necesario usar un rango específico).

Tenga en cuenta que MATCH con un "tipo de coincidencia" de -1 según la sugerencia de Chris debe tener valores descendentes en la columna B; esta fórmula funciona sin importar cómo esté ordenada la columna B.