Média de fórmulas do Excel sem células em branco

Question

Eu tenho uma linha de valores e quero encontrar a média dos três primeiros, sem contar as células em branco. Exemplo:

  A      B  C      D  E  F  G
 12  blank  8  blank  7  9  4

A média das 3 primeiras células não vazias deve ser 9, mas não consigo encontrar uma fórmula que calcule isso para mim. Alguém pode ajudar?

    

Answer 1

Em A2 digite:

=IF(A1="",0,1)

e em B2 digite:

=IF(OR(B1="",SUM($A$2:A2)=3),0,1)

e copie. Então, em outra célula, digite:

=SUMPRODUCT((1:1)*(2:2))/3

    

Answer 2

Você está procurando a função AVERAGE . De acordo com a fórmula sintaxe e uso para o AVERAGE do Excel função:

If a range or cell reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

A fórmula =AVERAGE(A1:E1) retorna 9 :

    

Answer 3

Solução 1

Força bruta: A média que você deseja pode ser calculada por:

=IF(COUNT($A1:$C1)=3, AVERAGE($A1:$C1),
 IF(COUNT($A1:$D1)=3, AVERAGE($A1:$D1),
 IF(COUNT($A1:$E1)=3, AVERAGE($A1:$E1),
 IF(COUNT($A1:$F1)=3, AVERAGE($A1:$F1),
 IF(COUNT($A1:$G1)=3, AVERAGE($A1:$G1), "oops")))))

(Single-line version:)
=IF(COUNT($A1:$C1)=3, AVERAGE($A1:$C1), IF(COUNT($A1:$D1)=3, AVERAGE($A1:$D1), IF(COUNT($A1:$E1)=3, AVERAGE($A1:$E1), IF(COUNT($A1:$F1)=3, AVERAGE($A1:$F1), IF(COUNT($A1:$G1)=3, AVERAGE($A1:$G1),"oops")))))

que procura o subconjunto inicial mais curto da linha que contém três números e calcula a média disso. Isso se torna não apenas inábil para um grande número de colunas, mas pode realmente falhar rapidamente como (acredito) IF() tem um limite de aninhamento bastante baixo.

Solução 1.9

Finesse.

Chamo isso de "1,9" porque ainda não está pronto. A expressão

SMALL(IF(ISNUMBER($A1:$G1),COLUMN($A1:$G1),""), {1,2,3})

(usado em uma fórmula de matriz) localiza os números das colunas dos três primeiros números da linha 1. O

IF(ISNUMBER($A1:$G1),COLUMN($A1:$G1),"")

parte avalia para

IF( {TRUE,FALSE,TRUE,FALSE,TRUE,TRUE,TRUE}, {1,2,3,4,5,6,7}, "" )

que reduz para

{ 1, "", 3, "", 5, 6, 7 }

e SMALL(…, {1,2,3}) do acima produz { 1, 3, 5 } . Deve ser uma simples questão de dizer

=AVERAGE(INDEX($A7:$G7, 1, SMALL(IF(ISNUMBER($A7:$G7),COLUMN($A7:$G7),""),{1,2,3})))

ou

=AVERAGE(OFFSET($A7:$G7, 1, SMALL(IF(ISNUMBER($A7:$G7),COLUMN($A7:$G7),""),{1,2,3})))

mas eles retornam o valor de A1 (12) ou um erro ( #DIV/0! ou #REF! ). Eu não consigo descobrir como terminar isso.