AVERAGEIFS valores que não são NA

Question

Eu tenho o seguinte AVERAGEIFS que a coluna de médias que eu agrupei pelos valores na coluna B

= AVERAGEIFS(B:B, A:A, A:A)

O problema é que, às vezes, a coluna B às vezes contém um erro NA() . Como faço para filtrar estes fora da equação?

Eu tentei fazer um filtro

= AVERAGEIFS(ARRAYFORMULA(IF(ISNUMBER(B:B),B:B)), A:A,A:A)

mas isso me dá um erro.

Aqui estão alguns dados de amostra:

* A    *  B  *
| Fred |  8  |
| Bill |  9  |
| Jane |  NA |
| Jane |  9  |
| Fred |  NA |
| Bill |  5  |
| Fran |  9  |
| Fred |  4  |
| Jane |  1  |

O que eu quero é adicionar uma coluna C, que mostra ao lado de cada linha, a média da pessoa sem contar os NAs.

* A    *  B  * C *
| Fred |  8  | 6 |
| Bill |  9  | 7 |
| Jane |  NA |   |
| Jane |  9  | 5 |
| Fred |  NA |   |
| Bill |  5  | 7 |
| Fran |  9  | 9 |
| Fred |  4  | 6 |
| Jane |  1  | 5 |

Quanto aos pontos em branco, eu não ligo muito para o que acontece lá - pode ser um erro, ou um espaço em branco, desde que o NA s não afete a média, eu não me importo.

    

Answer 1

Opção 1 - IFNA () com matriz

Eu realmente não entendo o que seu AVERAGEIFS deveria estar fazendo, mas tudo o que você precisa fazer para corrigir isso é usar o IFNA() junto com uma fórmula de matriz ( Ctrl + Shift + Enter ao inserir a fórmula ):

= AVERAGEIFS(IFNA(B:B,""), A:A, A:A)

Isso transforma valores de NA() em espaços em branco nos dados que você está manipulando.

Opção 2 - Limpar seus dados

Normalmente, essa seria minha opção 1, mas estou ciente de que não é o que você pediu e, por isso, a rebaixei para 2 ...

Obviamente, toda a fórmula acima é substituída por NA() com valores em branco. Alternativamente, por que não apenas inicialmente calcular suas células como espaços em branco? Isso fará com que suas colunas fiquem mais bonitas também.

Seja qual for o cálculo que você tenha nas células base, altere a opção NA() para retornar "" ou envolva o cálculo inteiro em IFNA([insert calculation here],"")

    

Answer 2

Uma maneira não tão inteligente será adicionar uma nova coluna oculta (digamos, coluna D) com:

=IF(ISNUMBER(B:B),B:B,0)

Em seguida, o seu =AVERAGEIFS(D:D,A:A,A:A) funcionará como pretendido:

Fred    8   4
Bill    9   7
Jane    NA  3.333333333
Jane    9   3.333333333
Fred    NA  4
Bill    5   7
Fran    9   9
Fred    4   4
Jane    1   3.333333333