Формула Excel IF/AND с минимальными расчетными сроками

Question

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

(не низкий доход)

Сумма к оплате: $ 1-150, срок: 6 недель

Причитающаяся сумма: 151-300 долларов США, сроки: 10 недель

Сумма к оплате: $ 301-500, срок: 12 недель

Причитающаяся сумма: 501-700 долларов США, сроки: 14 недель

Сумма к оплате $ 701-1000, срок: 18 недель

(низкий уровень дохода)

<= $ 500/$ 25(рассрочка)= количество месяцев (максимум 18 месяцев)

= $ 501/$ 50(рассрочка)= количество месяцев (максимум 18 месяцев)

Вот формула, которая у меня есть, которая работает только для части взноса в 501 долл. США /25 долл. США и части с низким доходом: = IF(AND(B2 = "Y"), MIN(D2+546,(((C2 /25)* 30))+D2)), ЕСЛИ (И (С2> = 1, С2 <= 150), D2+6 * 7, ЕСЛИ (И (С2> = 151, С2 <= 300), D2+10 * 7, ЕСЛИ ( И (С2> = 301, С2 <= 500), D2+12 * 7, ЕСЛИ (И (С2> = 501, С2 <= 700), D2+14 * 7, ЕСЛИ (И (С2> = 701), D2+18 * 7))))))

Я не могу заставить оба $ 501/$ 50 вписаться в формулу. 546 представляет 18 месяцев в днях. Любая помощь будет принята с благодарностью!

Критерии электронных таблиц Критерии2

Answer 1

Мы можем упростить формулу и использовать некоторые другие функции, чтобы вернуть желаемую сумму для добавления к дате:

=D2+IF(B2="Y",MIN(546,(C2/((C2>500)*25+25))*30),LOOKUP(C2,{0,151,301,501,701},{6,10,12,14,18})*7)

Поэтому, если Y , то мы берем минимум 546 дней, а дни, возвращаемые (C2/((C2>500)*25+25))*30) .

(C2>500) вернет 1 если истина, и 0 если ложь, которая затем умножается на 25 и прибавляется к 25, чтобы вернуть либо 50 либо 25 знаменателю.

Если N тогда произойдет поиск, который, поскольку он является относительным, вернёт правильное количество недель в зависимости от того, где находится значение в первом массиве.

Answer 2

@ Рей Джуна и @ Скотт Крейнер, большое спасибо! Я закончил тем, что использовал:

= ЕСЛИ (В2 = "Y", то ПЧ (С2 <= 500, MIN (ДАТАМЕС (D2,18), (((С2/25)* 30)+D2)), MIN (ДАТАМЕС (D2,18), (((С2/50)* 30)+D2))), ЕСЛИ (С2 <= 150, D2+42, ЕСЛИ (С2 <= 300, D2+70, ЕСЛИ (С2 <= 500, D2+84, ЕСЛИ (С2 <= 700, D2+98, D2+126)))))

Спасибо за то, что также замечает мою ошибку на столе, snip img был на самом деле неверным, поэтому я обновил его. Мне не удалось использовать EDATE для расчета месяца с делением, но я смог использовать его в качестве ограничения на 18 месяцев. В моем случае 30 дней это хорошо, потому что у меня есть щедрый льготный период для оплаты счетов.

Answer 3

=IF(B2="Y",IF(C2<=500,MIN(D2+546,(((C2/25)*30)+D2)),MIN(D2+546,(((C2/50)*30)+D2))),IF(C2<=150,D2+6*7,IF(C2<=300,D2+8*7,IF(C2<=500,D2+12*7,IF(C2<=700,D2+14*7,D2+18*7)))))

Я упростил некоторые термины и скорректировал сроки для 151-300 долларов, чтобы согласовать их с вашим столом (8*7 вместо 10*7), но единственное, чего вам действительно не хватало, это IF(C2<=500,MIN(D2+546,(((C2/25)*30)+D2)))),MIN(D2+546,(((C2/50)*30)+D2))) , так как вам нужно было проверить сумму задолженности если низкий доход.

Я оставил здесь термины умножения, но если у вас есть большая таблица этих формул, я рекомендую заменить их фактическими значениями, такими как замена +8*7 на +42 . Даже небольшие расчеты могут привести к замедлению работы таблицы.

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