Como faço para calcular a média dos últimos 10 valores de uma linha contendo espaços em branco

Question

Estou tentando calcular handicaps de golfe para uma liga. Há 30 semanas no cronograma e apenas as 10 pontuações mais recentes são usadas para calcular o handicap. Os dados são assim:

Estou usando esta fórmula para calcular handicaps: =ROUND((AVERAGE(Q3:AD3)-54)*0.8,0)

Atualmente eu tenho que ajustar o intervalo para cada jogador, a cada semana para incluir apenas as últimas 10 pontuações. Como posso melhorar essa fórmula para fazer isso por mim?

    

Answer 1

Você precisa de uma fórmula mais complexa para considerar as últimas 10 células com número:

=ROUND((AVERAGE(OFFSET(B2,0,LARGE(IF(B2:W2>0,COLUMN(B2:W2),""),10)-COLUMN(B2),1,COLUMN(W2)+1-LARGE(IF(B2:W2>0,COLUMN(B2:W2),""),10)))-54)*0.8,0)

onde:

• LARGE(IF(B2:W2>0,COLUMN(B2:W2),""),10) obtém o número da coluna do primeiro número dos 10
• OFFSET(B2,0,LARGE(...)-COLUMN(B2),1,COLUMN(W2)+1-LARGE(...),10))) cria uma referência ao intervalo contendo os 10 números
• =ROUND((AVERAGE(OFFSET(...)-54)*0.8,0) é o cálculo de acordo com sua fórmula original

Esta é uma fórmula de array, então após inserir você precisa pressionar Ctrl + Deslocar + Enter

A fórmula será atualizada automaticamente se você inserir / excluir colunas de / para depois da primeira coluna referenciada e antes da última coluna referenciada.