Необходимо иметь возможность ввести 2 (а в перспективе и больше) переменных и найти запись (дату), которая наиболее точно соответствует ОБЕИМ.
Вот мои переменные:
Вот пример моих записей:
Для ясности, я не хочу, чтобы ближайшее совпадение для '3 mo chg' было из одной даты, а 'Avg Vol 3m' из другой. Мне нужна ОДНА запись даты, которая лучше всего соответствует ОБЕИМ переменным. Я понимаю, что, вероятно, будет некоторая субъективность в том, что можно квалифицировать как "лучшее" совпадение. Это нормально, просто ищу некоторые идеи.
Каков наилучший способ этого добиться?
решение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
Надеюсь, это поможет, и удачи вам.