Ближайшее совпадение для двух переменных

Question

Необходимо иметь возможность ввести 2 (и, в конечном итоге, больше) переменных и найти запись (дату), которая наиболее близко соответствует ОБА.

Вот мои переменные:

Вот образец моих записей:

Для пояснения, я не хочу, чтобы наиболее близкие совпадения для '3 mo chg' были от одной даты, а 'Avg Vol 3m' - от другой. Мне нужна ОДНА запись даты, которая лучше всего подходит ОБА переменным. Я понимаю, что, вероятно, будет определенная субъективность в том, что считается "лучшим" матчем. Это нормально, просто ищу идеи.

Какой лучший метод для достижения этой цели?

Answer 1

Вы хотите найти строку с минимальным общим отклонением от указанных вами значений. Формула, подобная этой, сделает это.

=ABS((E2-B$1)/B$1)+ABS((F2-B$2)/B$2)

Это вычисляет процентное отклонение от каждого значения, берет абсолютное значение и складывает их вместе. В таблице ниже показан столбец "помощник" с общим отклонением. Ближайшим совпадением является строка, в которой отклонение является наименьшим.

Формула в условном форматировании =IF(G2=MIN(G$2:G$11),1,0) делает дату зеленой для минимального отклонения (я не могу заставить ее работать для всей строки - комментарии приветствуются).

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

Редактировать: статистик, вероятно, будет настаивать на вычислении "суммы квадратов" отдельных отклонений, а не суммы абсолютных значений.

=((E2-B$1)/B$1)^2+((F2-B$2)/B$2)^2

Надеюсь, это поможет, и удачи.