Como identificar a coluna onde o valor cai abaixo de 0

Question

Estou tentando descobrir uma fórmula para identificar em qual coluna o valor fica abaixo de 0.

Eu tenho milhares de linhas e dezenas de colunas, portanto, fazer isso manualmente não é uma opção

Então, se minhas colunas e valores forem os seguintes:

    A   B   C   D
1   Jul Aug Sep Oct
2   25  10  5   -2
3   10  2   -6  -10

Portanto, para a linha 2, gostaria de devolver o resultado "Oct" (ou 4 como a quarta coluna), pois este é o mês em que o valor desce abaixo de 0. Para a linha 3, gostaria de devolver o valor "Sep" ( ou 3 como a terceira coluna).

Alguma idéia?

    

Answer 1

Você pode fazer isso com uma fórmula de matriz. Na linha 2, selecione sua célula onde deseja a fórmula, cole o seguinte na barra de fórmulas e pressione Ctrl + Deslocar + Enter .

=INDEX($A$1:$D$1,MIN(IF(A2:D2<0,COLUMN(A2:D2))))

Para referência, A1:D1 é o intervalo de cabeçalhos de coluna (meses) e A2:D2 são os valores na linha. O que essa fórmula faz é procurar por valores menores que 0 e, em seguida, usa aquele com o menor número de coluna. Em seguida, ele retorna o cabeçalho do mês nesse número de coluna.

Esta fórmula pode ser preenchida em todas as linhas.

EDITAR:
Se os seus dados não começarem na coluna A, você terá que fazer um pequeno ajuste na fórmula para fazê-lo funcionar. Tudo permanece o mesmo na fórmula, exceto que o primeiro argumento da função INDEX deve ser estendido para a coluna A. Por exemplo, se seus dados começarem na coluna C, você usaria a seguinte fórmula (inserida como uma fórmula de matriz) .

=INDEX($A$1:$F$1,MIN(IF(C2:F2<0,COLUMN(C2:F2))))

A razão para isso é que o segundo argumento da função INDEX indica a posição na matriz, não necessariamente o número da coluna. Essa distinção é obliterada se você apenas estender o array até a coluna A (isto é, a posição no array será igual ao número da coluna).

    

Answer 2

Provavelmente é um pouco mais simples usar o INDEX / MATCH, por ex. esta fórmula de matriz no AV10

=INDEX(AW$1:BM$1,MATCH(TRUE,AW10:BM10<0,0))

confirmado com CTRL + SHIFT + ENTER

ou se você preferir evitar o CSE, adicione outra função INDEX como essa e a fórmula pode ser digitada "normalmente"

=INDEX(AW$1:BM$1,MATCH(TRUE,INDEX(AW10:BM10<0,0),0))

Se não houver números < 0 então retornará o erro # N / A ...... ou você poderá usar a função IFERROR para substituir por algum texto, por exemplo

=IFERROR(INDEX(AW$1:BM$1,MATCH(TRUE,INDEX(AW10:BM10<0,0),0)),"No negative numbers")