Subtrair valores somente se todos forem números

Question

Eu quero subtrair quatro células no excel de uma célula específica, mas apenas se qualquer uma das quatro células tiver um valor. Mais especificamente no meu caso, a fórmula na célula L2 é atualmente

=IF(AND(ISNUMBER(E2),ISNUMBER(G2),ISNUMBER(I2),ISNUMBER(K2),),"",D2-E2-G2-I2-K2)

mas eu só quero que isso seja calculado quando qualquer uma ou todas as células E2, G2, I2, K2 tiverem um valor. O que estou perdendo na fórmula para fazer L2 retornar um 0 a menos que o acima seja verdadeiro?

    

Answer 1

O que você precisa fazer - e o que deveríamos ter feito há dois dias - é dar alguns exemplos de quais resultados você deseja para quais entradas. Sim, eu sei que você nos deu alguns mas eles deveriam ter sido editado na questão; os comentários são um péssimo lugar para dados tabulares e fórmulas longas, e eles são o lugar errado para informações isso é necessário para entender a pergunta. De qualquer forma, eu criei alguns dados de amostra para você. Note que eles não estão todos corretos. Eu sei que eles não estão todos corretos, porque eles são inconsistentes, mas também teria dificuldade em torná-los todos corretos, porque ainda estou lutando para entender o que você quer.

        Case       D        E        G        I        K        L (Result)
        fee       40        1                                  39
        fie       40        0.01                               39.99
        foe       40        0                                  40
        fum       40                                           40
        foo       40                                            0

Se os casos fee e fie não refletirem o que você deseja, temos um sério problema de comunicação. Se você nunca, nunca, em um zilhão de anos ter um 0 real em E2 , G2 , I2 ou K2 , então, não precisamos nos preocupar com o caso foe . Então, a pergunta de US $ 40.000 é: você quer fum ( L2 = 40) ou foo ( L2 = 0)?

fum ( L2 = 40)

Isso é simples. Defina L2 para qualquer um dos seguintes,

• =D2-E2-G2-I2-K2
• =D2-(E2+G2+I2+K2)
• =D2-SUM(E2,G2,I2,K2)

que são equivalentes.

foo ( L2 = 0)

Um pouco mais longo, mas tão óbvio:

=IF(OR(ISNUMBER(E2),ISNUMBER(G2),ISNUMBER(I2),ISNUMBER(K2)), D2-E2-G2-I2-K2, 0)

Isso pode parecer familiar, pois é a resposta de Teylyn combinada com meu comentário (“Se você quiser que a subtração seja calculada se alguma ou todas as células tiverem um valor,… use OR em vez de AND .”)

Huh? O que?

Sua fórmula original,

=IF(AND(ISNUMBER(E2),ISNUMBER(G2),ISNUMBER(I2),ISNUMBER(K2),),"",D2-E2-G2-I2-K2)

é (como Teylyn apontou) chamando a função AND() com cinco parâmetros:

• ISNUMBER(E2) ,
• ISNUMBER(G2) ,
• ISNUMBER(I2) ,
• ISNUMBER(K2) e
• (em branco).

Bem, se um valor em branco for usado como um número no Excel, é tratado como se fosse 0 (zero). É por isso que as fórmulas “ fum ” funcionam: as células em branco são tratadas como se contivessem 0. Da mesma forma, se um valor em branco for usado como um valor booleano (isto é, lógico) no Excel, é tratado como se fosse FALSO. Então sua fórmula age como

=IF(AND(ISNUMBER(E2),ISNUMBER(G2),ISNUMBER(I2),ISNUMBER(K2),FALSE),"",D2-E2-G2-I2-K2) 

Como AND(anything, FALSE) é FALSE , o acima é reduzido para

=IF(FALSE, "", D2-E2-G2-I2-K2)

ou simplesmente

=D2-E2-G2-I2-K2

Isso pode parecer familiar, pois é a fórmula " fum ".

Já estamos lá?

Se eu perdi algum aspecto misterioso da sua pergunta, por favor, edite sua pergunta para esclarecê-la; por exemplo, adicionar dados de amostra em que nenhuma das respostas acima dá os resultados que você quer.

    

Answer 2

Sua fórmula retorna uma string vazia quando todas as condições são atendidas. Eu acho que você quer o cálculo naquele lugar. Existe uma vírgula perdida na função AND ().

De acordo com sua descrição verbal, a fórmula que você provavelmente precisa é

=IF(AND(ISNUMBER(E2),ISNUMBER(G2),ISNUMBER(I2),ISNUMBER(K2)),D2-E2-G2-I2-K2,0)

Isso só fará o cálculo se todas as células contiverem um valor.

but I only want this to be calculated when any or all of cells E2,G2,I2,K2 has a value.

Você precisa se decidir. Se você quiser fazer o cálculo quando qualquer célula contiver um valor, você não precisará de uma instrução IF. Nesse caso, basta fazer a subtração.

    

Answer 3

Estou pensando que você pode precisar de uma linha auxiliar. Você poderia colocá-lo logo abaixo das células em questão usando a fórmula

=IF(ISNUMBER(E2),E2,0)

. Então você poderia simplesmente fazer uma subtração simples na linha auxiliar. É claro que essa fórmula seria inserida no E3. E BTW, uma instrução AND só é verdadeira se todos os seus argumentos forem verdadeiros, então sua fórmula só funcionaria quando todas as células contivessem números. Se apenas uma célula contivesse um valor não numérico, a fórmula seria avaliada como falsa e não calculada. FWIW Falso = 0; Verdadeiro = não falso.