como faço para calcular a média dos 3 números mais baixos dos 6 números finais em uma linha de 22? Algumas células podem estar em branco

Question

Como classifico automaticamente o menor 3 dos últimos 6 números em uma linha de 22 que adiciona um número por semana? Obviamente, eu preciso de pelo menos 7 números para começar a usar essa fórmula. Além disso, algumas células em algumas linhas podem estar em branco.

    

Answer 1

Use uma coluna auxiliar com essa fórmula

= SE (A2, ROW (), 0)

Copie para baixo.

Em seguida, use esta fórmula de matriz, confirmada com Ctrl - Deslocamento - Enter

=AVERAGE(SMALL(IF((A2:A15<>0)*(B2:B15=LARGE(B2:B15,{1,2,3,4,5,6})),A2:A15),{1,2,3}))

A função Grande fixa os seis maiores números na coluna B. A função If retorna os respectivos números na coluna A. A função Small retorna os três menores desse conjunto. E Média faz a média destes.

É assim que funciona no Excel. Não tenho certeza sobre o LibreOffice Calc.

Editar: aqui está uma variante que funciona sem uma coluna auxiliar:

=AVERAGE(SMALL(IF((A2:A15<>0)*(IF(ISNUMBER(A2:A15),ROW(A2:A15))=LARGE(IF(ISNUMBER(A2:A15),ROW(A2:A15)),{1,2,3,4,5,6})),A2:A15),{1,2,3}))

Também confirmado com Ctrl - Deslocamento - Enter

    

Answer 2

Uma maneira de fazer isso seria usar a função "pequena". Não tenho certeza se você pode retornar os três valores mais baixos ao todo, mas você pode usar três células diferentes com "classificações" variadas de "pequenas" e, em seguida, considerar a média dessas células juntas:

[Let's say you have a column of numerical data B3:B80...]
[Sample data from B column: 19, 23, 23, 24, 24, 27, 27, 27, 28, 29, 29, 29...]

[cell on left, cell contents in quotes, output on right of =>]

   C3 '=SMALL(B3:B80;3)' => 23 (third lowest number)
   C4 '=SMALL(B3:B80;2)' => 23 (second lowest number)
   C5 '=SMALL(B3:B80;1)' => 19 (first lowest number; you could also used "MIN")
   C6 '=AVERAGE(C3:C5)' => 21.67

    

Answer 3

Eu percebo que esta não é uma resposta prática, mas talvez alguém possa limpá-la (talvez com uma fórmula de matriz inteligente?):

=IF(COUNT(B75:B80)=6, AVERAGE(SMALL(B75:B80,1),SMALL(B75:B80,2),SMALL(B75:B80,3)),
 IF(COUNT(B74:B80)=6, AVERAGE(SMALL(B74:B80,1),SMALL(B74:B80,2),SMALL(B74:B80,3)),
 IF(COUNT(B73:B80)=6, AVERAGE(SMALL(B73:B80,1),SMALL(B73:B80,2),SMALL(B73:B80,3)),
 IF(COUNT(B72:B80)=6, AVERAGE(SMALL(B72:B80,1),SMALL(B72:B80,2),SMALL(B72:B80,3)),
    ...))))))))