Tenho dados categorizados por dois critérios, começando em B2, com cabeçalhos em B1 - > BV1 e A2 - > A101.
A célula A1 contém uma tag para os dados; "MYDATA".
Células B1 - > BV1 são fatores de 6: 0, 6, 12, 18 ... 354. Estes são bin começa tal que 0 representa dados em que 0 < = data < 6 Células A2 - > A101 são fatores de 0,25: 0,25, 0,50, 0,75 ... 49,75. Estes são valores médios de bin tal que 0,25 representa dados em que 0,00 < = dados < 0,50.
Aqui está um instantâneo:
MYDATA 0 6 12 18
0.25 0.014 0.013 0.011 0.010
0.75 0.135 0.122 0.120 0.120
1.25 0.451 0.434 0.442 0.442
Eu gostaria de rebater esses dados em menos escaninhos, calculando a média inerentemente quando fizer isso.
Gostaria de rebitar as linhas por inteiro como 0 - > 1, 1 - > 2 etc até 30 (dados acima de 30 no original para serem excluídos). Começando em B6 = 0 (então B7 = 1 até B36 = 30) & Rebinando as colunas por valores de 30, então 0, 30 ... 330. Começando em C5 = 0, então D5 = 30 até N5 = 330.
Estou tentando fazer isso com o AVERAGEIFS usando a seguinte fórmula inserida na célula C6 (colunas e linhas bloqueadas com $ pronto para arrastar para formar uma tabela completa):
AVERAGEIFS('DATA'!$A$1:$BI$101,'DATA'!$B$1:$BI$1,">"&C$5,'DATA'!$B$1:$BI$1,"<="&D$5,'DATA'!$A$2:$A$101,">"&$B6,'DATA'!$A$2:$A$101,"<"&$B7)
Isso está retornando o erro #VALUE. Na tentativa de resolver isso, usei a opção "Avaliar fórmula" em Fórmulas - > Avaliar fórmula - > Avalie. Isso destaca meu primeiro critério, > C5 (escrito como ">" & C5) e ao pressionar "Avaliar", altera para ">" & 0.
Olhando meus dados, ele está puxando o valor correto para C5; 0. No entanto, não entendo por que isso está retornando o erro #VALUE. No meu entender, esse é o primeiro critério que define o início do intervalo de colunas selecionado em MYDATA. O final do intervalo de colunas é então dado por D5 (= 30).
Qual é o erro no meu entendimento ou na minha abordagem?
Você tem critérios em colunas e linhas. Para esse tipo de configuração, você não pode usar AVERAGEIFS , mas pode usar uma "fórmula de matriz" como esta:
=AVERAGE(IF('DATA'!$B$1:$BI$1>C$5,IF('DATA'!$B$1:$BI$1<=D$5,IF('DATA'!$A$2:$A$101>$B6,IF('DATA'!$A$2:$A$101<$B7,'DATA'!$B$2:$BI$101)))))
confirmado com CTRL + SHIFT + ENTER
Nota 1: o intervalo para a média está no final.
Nota 2: talvez você precise de <=$B7 caso contrário, você perderá alguns valores