В моей текущей электронной таблице Excel у меня есть дата в столбце A и цены закрытия S & P 500 в столбце B.
Я пытаюсь написать формулу, которая возвращает дату в последний раз, когда цена закрытия была больше или равна сегодняшней цене закрытия.
Я пытался использовать SUMPRODUCT, MATCH, VLOOKUP и INDEX, но безрезультатно. Какие-либо предложения?
Вот как выглядит моя таблица:
Я хочу, чтобы формула вошла в ячейку B8.
Хорошим первым шагом является определение строки самой последней цены закрытия, которая была больше или равна сегодняшней цене закрытия. Посмотрите на все строки, где цена закрытия была больше или равна сегодняшней цене закрытия. В ваших данных выборки они представляют собой строки 13, 14, 16, 17, 19, 20, 22, 23 и 24 (и, вероятно, больше, возвращаясь во времени назад). Какой из них самый последний? Это 13, потому что это самый маленький (минимальный) номер строки в списке.
Теперь давайте посмотрим, сможем ли мы построить этот список. Кажется, мы хотим оценить
IF( Bn >= B12, n, (nothing) )
Мы можем сделать что-то подобное:
=MIN(IF(B13:B100>=B$12, ROW(B13:B100)))
заменить 100 на число, которое больше, чем самый большой номер строки, который может когда-либо содержать данные.
(Я предполагаю, что в вашем случае вы могли бы использовать 365, или 260 (52 × 5), или кратное одному из них, если у вас есть данные за несколько лет.)
Выше приведена формула массива. Поэтому, чтобы заставить его работать, вы должны нажать Ctrl+Shift+Enter после ввода формулы.
Но вы не хотите номер строки; Вы хотите данные. Эта часть проста:
=INDEX($A:$A, MIN(IF(B13:B100>B$12, ROW(B13:B100))))
Опять же, вы должны ввести Ctrl+Shift+Enter после ввода формулы.