Пара возможных подходов:
ОПЦИЯ 1
1) Сортировка данных по значению X и сортировка по убыванию. Вы хотите, чтобы ваше наибольшее значение X было вашей первой точкой.
2) Определите наклон линии между каждой последующей точкой. В соседнем столбце.
3) Когда наклон линии рассчитывается, но превышает максимальное значение для точки 1 и минимальное значение для точки два (или наоборот), вы знаете, что вы находитесь за пределами вашей точки схождения.
ВАРИАНТ 2
1) Сортировка данных по значению X в порядке убывания. так же, как вариант1
2) Если у вас есть способ определить значение сходимости, поместите его в ячейку.
3) Найдите первое значение Y в списке, которое превышает значение сходимости на + или - ваш допуск.
НАЙТИ ПЕРВОЕ ПРОИЗВОДСТВО
Чтобы найти первое вхождение чего-либо, вам нужно использовать либо формулу массива, либо формулу, которая выполняет вычисления, подобные массиву. Я попытаюсь проиллюстрировать, используя функцию AGGREGATE, но есть и другие методы. Поскольку AGGREGATE выполняет вычисление массива для выбранных опций, избегайте использования полных ссылок на столбцы в функции AGGREGATE, иначе могут быть сделаны чрезмерные вычисления, которые могут замедлить работу вашей системы.
AGGGREGATE (Function #, Error/Hidden Options, Your formula, Parameter)
Выше приведено основное описание функции AGGREGATE. То, что мы собираемся сделать, это сказать AGGREGATE отсортировать результаты из нашей формулы в порядке возрастания, игнорировать любые результаты формулы, которые являются ошибками, а затем дать нам первую запись в отсортированном списке. Хотите верьте, хотите нет, но большинство из них довольно просты. Я оставлю это пока, и сосредоточусь на сложной части ... нашей формуле.
То, что мы ищем, является первым появлением чего-либо. Нам либо понадобится значение этого чего-либо или его позиция в списке (или номер строки). Обычно в этой ситуации то, что я пытаюсь найти, обычно ставят в формулу в качестве числителя. Любые условия, которые должны быть выполнены и дают результаты ИСТИНА или ЛОЖЬ, помещаются в знаменатель формулы.
Хитрость заключается в том, что когда TRUE или FALSE помещаются в математическую операцию, они конвертируются в 1 для TRUE и в 0 для FALSE. (В целом, если такие функции, как IF, ищут ИСТИНА и ЛОЖЬ при принятии решений, 0 обрабатывается как ЛОЖЬ, а КАЖДЫЙ ДРУГОЙ НОМЕР ИСТИНА.) Таким образом, каждый раз, когда вы делите на FALSE, вы будете делить на 0, что приводит к ошибке для расчета, которая ранее была заявлена, что AGGREGATE будет установлен на игнорирование.
Таким образом, при правильном построении формулы вы получите отсортированный список элементов, которые соответствуют только вашим условиям в знаменателе. И поскольку это отсортированный список в порядке возрастания, когда он настроен на возврат 1-й записи, вы получите (в нашем случае) наименьший номер строки или наименьшее значение.
Пример. Предположим, что ваши значения X находятся в столбце B, а значения Y - в столбце C, а ваши данные - в строках 2: 100. Мы хотим найти первое значение Y, которое больше, чем 2,4 от 81,3
=AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-81.3)>=2.4),1)
Теперь, чтобы вернуть значение этой ячейки, вы должны встроить формулу AGGREGATE в формулу INDEX.
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-81.3)>=2.4),1))
Обратите внимание, что полная ссылка на столбец B:B находится вне функции AGGREGATE, что в порядке. Если вы хотите узнать, что это за значение Y, измените B:B на C:C.
Исходя из ваших критериев от 101 до 99, вы можете использовать:
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-100)>=1),1))
OR
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(($C$2:$C$100>=101)*($C$2:$C$100<=99)),1))
Эти уравнения дадут вам значение первой точки за пределами ваших пределов. Таким образом, чтобы получить последнюю точку внутри ваших пределов, вам нужно вычесть 1 из номера строки, что дает новую формулу:
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(ABS($C$2:$C$100-100)>=1),1)-1)
OR
=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$100)/(($C$2:$C$100>=101)*($C$2:$C$100<=99)),1)-1)
Обратите внимание на * между двумя проверками условий в знаменателе. * Действует как функция AND (только 1 * 1 = 1; оба условия должны выполняться). Знак « + будет действовать как функция «ИЛИ» (либо результат, либо оба могут быть истинными, если результат не равен нулю).
