как мне усреднить 3 самых младших из последних 6 чисел подряд из 22? Некоторые ячейки могут быть пустыми

Question

Как автоматически усреднить 3 самых младших из последних 6 чисел в ряду из 22, добавляя одно число в неделю? Очевидно, мне нужно по крайней мере 7 чисел, чтобы начать использовать эту формулу. Кроме того, некоторые ячейки в некоторых строках могут быть пустыми.

Answer 1

Используйте вспомогательный столбец с этой формулой

= ЕСЛИ (А2, СТРОКА (), 0)

Скопируйте вниз.

Затем используйте эту формулу массива, подтвержденную Ctrl - Shift - Enter

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

Функция Large фиксирует шесть самых больших чисел в столбце B. Функция If возвращает соответствующие числа в столбце A. Функция Small возвращает три наименьших числа из этого набора. И Среднее делает среднее из этих.

Вот как это работает в Excel. Не уверен насчет LibreOffice Calc.

Изменить: вот вариант, который работает без вспомогательного столбца:

=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}))

Также подтверждается Ctrl - Shift - Enter

Answer 2

Один из способов сделать это - использовать "маленькую" функцию. Я не уверен, что вы можете вернуть три самых низких значения в целом, но вы можете использовать три разные ячейки с разными "рангами" "маленьких", а затем взять среднее из них вместе:

[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

Я понимаю, что это не практический ответ, но, может быть, кто-то может его очистить (может, с помощью умной формулы массива?):

=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)),
    ...))))))))