Excel - calculando a média das células subseqüentes usando a primeira célula não usada na coluna

Question

Bom dia,

Eu preciso que esta saída esteja em uma folha separada, mas essencialmente o problema que estou tentando resolver pode ser explicado da seguinte forma.

Estou tentando descobrir como calcular a média de células em uma coluna com o seguinte formato:

Em B2 na Planilha2, gostaria que o average(Sheet1!a2:a5) , em B3 , average(Sheet1!a6:a9) , em B4 , (Planilha1! a10: a13), etc.

Eu poderia ter feito isso muito mais complicado do que o necessário, mas minha ideia era usar a função Offset da seguinte forma:

=AVERAGE(OFFSET(Sheet1!$a$2, 4*(ROW()-ROW(Sheet1!$a$2)), 0, 4, 1))

Que funcionou! No entanto, preciso de outra coluna para mostrar dados diferentes, mostrados abaixo:

=AVERAGE(OFFSET(Sheet1!$a$118, 4*(ROW()-ROW(Sheet1!$a$118)), 0, 4, 1))

O que me dá um erro de referência. Alguém pode me ajudar a entender por que isso não está funcionando? Uma solução fácil seria apreciada, mas uma explicação de por que seria especialmente grande !!

    

Answer 1

Certo, então vamos usar uma fórmula diferente para o seu primeiro -

=AVERAGE(INDEX(A:A,1+4*(ROW()-ROW($B$1))):INDEX(A:A,4*(ROW()-ROW($B$1)+1)))

O que isto faz é criar um array para a média funcionar. Ele faz isso localizando a linha atual, subtraindo a linha de referência, adicionando um e multiplicando 4, para cada lado do intervalo. Use a fórmula de avaliação para vê-lo em ação.

Digamos que você queria fazer isso novamente, mas a partir de A118, que é o que eu acho que você está perguntando

=AVERAGE(INDEX(A118:A999,1+4*(ROW()-ROW($B$118))):INDEX(A118:A999,4*(ROW()-ROW($B$118)+1)))

Agora, isso exige que você faça seu trabalho na linha 118, mas diga que deseja apenas iniciar uma nova coluna na linha 1 -

=AVERAGE(INDEX(A118:A999,1+4*(ROW()-ROW($B$1))):INDEX(A118:A999,4*(ROW()-ROW($B$1)+1)))

Assim como o primeiro.