Динамические формулы, основанные на результатах других формул ячейки

Question

У меня за 10 лет ежемесячные данные по 300 растениям. В то время как Excel может выполнять скользящие средние для разных периодов времени, я собираюсь сделать скользящее среднее значение для этого набора данных.

Очевидно, я могу вычислить формулу с помощью грубой силы, но я искал возможность использовать формулу, которая может быть изменена на основе результатов другой ячейки.

Если у меня есть формула для скользящего медианного значения за 4 квартала для данных за 10 лет = медиана (c3:c951), то следующий период будет = медиана (c952:c1900) и т.д.

Вместо того, чтобы переписывать каждую формулу в ячейках, казалось бы, я должен иметь возможность ссылаться на строку чисел или результатов, чтобы заменить фактическое число в формуле.

Мне кажется, много лет назад я видел, что сделано, но, конечно, не могу вспомнить, как это сделать.

Answer 1

Насколько я могу судить, вы используете каждую (n = 948) строку? Должно быть что-то вроде этого -

=MEDIAN(INDIRECT("C"&(ROW()-1)*948+ROW(C2)+1&":C"&ROW()*948+ROW(C3)))

Так что, если вы положите его в E1, он выйдет

=MEDIAN($C$3:$C$951)

перетащил на E2, он вернется

=MEDIAN($c$952:$c$1900)

так далее

---

Чтобы избежать INDIRECT вы можете использовать что-то вроде -

=MEDIAN(OFFSET(C$3,ROWS(E$1:E1)*948-948,0,948))

---

Использовать календарные годы и учитывать високосные годы - я не знаю.

Answer 2

Используйте функцию OFFSET. Вы можете сделать свою математику там. Возможно в сочетании с функцией ROW.

Если данные находятся в столбце A, а ваши медианные метки (например, 2010, 2011 и т.д.) В столбце F, попробуйте что-то вроде:

=MEDIAN( OFFSET( $A$1; 365* ROW( F1 ); 0; 365; 1) )

И скопировать-вставить вниз.

Это вычисляет медиану для каждого блока из 365 элементов данных. Настройте ссылки на ячейки и магические числа в соответствии со своими потребностями.