Média dos valores numéricos em que uma coluna de identificador atende a uma condição no Excel

Question

No Excel 2007, tenho uma tabela com números nas colunas F, G, H, I e J. Há um identificador na Coluna B.

Eu preciso da média de todos os números nas colunas F, G, H, I e J, onde o identificador na coluna B = A14 (ou seja, o valor em A14).

Eu tentei o seguinte:

=AVERAGEIFS('Hours-Backup'!F3:J1048576,'Hours-Backup'!B:B,'Group Dashboard'!A14)

Isso resulta em um erro #VALUE! .

Por fim, quero fazer com que a função ignore todas as células que não sejam numéricas ou em branco. Em outras palavras, não quero supor que as células em branco sejam 0 para fins de média.

Eu não consigo descobrir qual é o problema.

    

Answer 1

Dois problemas. (Ou um problema com duas cabeças.) AVERAGEIFS help diz:

• Unlike the range and criteria arguments in the AVERAGEIF function, in AVERAGEIFS each criteria_range must be the same size and shape as sum_range.

Bem, isso é um erro de digitação: "sum_range" deve ser "average_range". Mas, de qualquer forma, esse é o seu problema: seu intervalo de critérios é 1 × ∞ (uma coluna inteira) e seu alcance médio é de 5 × 1048574. Assim, você pode corrigir um dos problemas alterando B:B para B3:B1048576 , tornando-se uma faixa de 1 × 1048574.

Você poderia resolver o segundo problema replicando a coluna B nas colunas C , D , E e F , e, em seguida, usando B3:F1048576 como seu critério_range. Mas isso é loucura. A maneira razoável é definir uma coluna auxiliar (digamos, V ) é a média das colunas F - J na linha atual:

• Defina V3 para AVERAGE(F3:J3) ,
• e arraste para baixo.

Depois, obtenha sua média condicional calculando a média das médias:

=AVERAGEIFS('Hours-Backup'!V3:V1048576,'Hours-Backup'!B3:B1048576,'Group Dashboard'!A14)
(changes)                  ^  ^                        ^  ^^^^^^^

    

Answer 2

Minha abordagem para situações como essa é um par de colunas auxiliares e algumas fórmulas de matriz. Espero que eu tenha colocado esta folha de brinquedo da mesma maneira que a sua:

Aqui eu configurei A16 como a célula para definir quais linhas filtradas você deseja manter. B é a coluna de valores para filtrar, C a G são as colunas com valores que podem estar faltando. Eu conto o número de valores presentes em cada linha na coluna H e somo os valores que estão presentes na coluna I . As células H2 e I2 somam as respectivas colunas 'Count' e 'Sum' e, em seguida, a média resultante é calculada em K2 . Observe que as fórmulas nas colunas H e I são muito robustas para os valores de texto e erro na tabela.

Não tenho certeza da compatibilidade da versão da construção SUM(IF(...)) nas fórmulas de matriz. Ele funciona claramente no Excel 2010 e provavelmente também deve funcionar no Excel 2007 e no Excel 2013.