Como extrair valores máximos de várias linhas e contar quantos caem para determinados critérios, na função de uma célula

Question

Eu tenho uma planilha no Excel que mostra o sucesso dos alunos em certas partes do exame (testes, apresentações, exame final, etc.). Eu preciso calcular alguns dados estatísticos e o problema é que para alguns testes eles têm três tentativas, apenas os pontos mais altos contam e eu não tenho a coluna "max". Preciso contar quantos deles tiveram mais de 15 pontos na melhor tentativa, quantos entre 10 e 14,9, etc. Também preciso ver quantos alunos tiveram pelo menos uma tentativa (com base em um campo não vazio ou > = 0).

A tabela se parece com isso (uma parte):
    Estudante .... Att1 .... Att2 .... Att3
    Anna ......... 10 ...... 14 ..... 13,5
    Belle ......... 7 ......... 15 ...... 15,5
    Clara ........ 17 ...... 18 ..... 16,5

Então, eu precisaria extrair em uma célula esse número de valores máximos por linha > = 15 é 2 (Belle em 15,5 e Clara em 18) e o total de alunos já tentou pelo menos uma vez é 3.

Repito que não posso adicionar a coluna separada onde eu calcularia os valores máximos porque é um documento oficial, e adicionar um a outro não é uma solução preferida.

Eu tentei algumas combinações de subtotal e offset, mas sem muito sucesso. Eu uso o MS Excel 2013, outros colegas usam 2007 e 2010.

    

Answer 1

Esta fórmula pode ser facilmente expandida para qualquer número de linhas ou colunas

=SUMPRODUCT((SUBTOTAL(4,OFFSET(B2:D2,ROW(B2:D10)-ROW(B2),0))>15)+0)

OFFSET retorna cada linha como um intervalo separado, SUBTOTAL encontra o valor máximo [4] em cada linha e SUMPRODUCT conta quantos desses valores são > 15.

Também é possível fazer o mesmo com COUNTIF em vez de SUBTOTAL, ou seja,

=SUMPRODUCT((COUNTIF(OFFSET(B2:D2,ROW(B2:D10)-ROW(B2),0),">15")>0)+0)

Para um intervalo, por ex. > 10 e < = 15 você pode ajustar a versão SUBTOTAL como esta

=SUMPRODUCT((SUBTOTAL(4,OFFSET(B2:D2,ROW(B2:D10)-ROW(B2),0))>{10,15})*{1,-1})

    

Answer 2

para a contagem de mais ou igual a 15 para uma linha (digamos nas colunas E, então E2 aqui)

=COUNTIF(B2:D2;">=15")

para você max (aqui no E5)

=countif(E2:E4;">0")

amostra

student at1 at2 at3     up15
anna    10  14  13,5    0
Belle   7   15  15,5    2
clara   17  18  16,5    3
                 Max    2

pode adicionar um teste por pelo menos 3 tentativas por linha, mas não está claro onde você deseja (por linha ou no máximo)