Умножение различных факторов на основе значения ячейки в Excel

Question

Я пытаюсь построить формулу, которая будет рассчитывать следующее:

=< 10,000 x 1.00

10,0001-12,500 x 1.25

12,501 - 15,000 x 1.50

Примеры:

Если число 8520 (8520 х 1,00) = 8520

Если число было 11 560 (10 000 x 1,00) + (1 560 x 1,25) = 11 950

Если число равно 13 500 (10 000 x 1,00) + (2500 x 1,25) + (1000 x 1,50) = 14 625

Answer 1

Хорошо, это было немного сложнее, чем казалось на первый взгляд.

создайте таблицу, подобную следующей:

Затем используйте следующую формулу массива:

=SUM(IF(A2>$D$2:$D$4,IF(A2<$E$2:$E$4,A2-$D$2:$D$4,$E$2:$E$4-$D$2:$D$4)*$F$2:$F$4,0))

Будучи формулой массива, она должна быть подтверждена с помощью Ctrl-Shift-Enter вместо Enter при выходе из режима редактирования. Если все сделано правильно, тогда Excel поместит {} вокруг формулы.

---

Основываясь на формуле @ fixer и делая формулу динамической, мы создаем таблицу следующим образом:

Как вы можете видеть, Фактор теперь является увеличением по сравнению с предыдущим, а не фактическим фактором.

Тогда мы можем использовать эту формулу:

=A2+SUMPRODUCT((A2>$D$3:INDEX(D:D,MATCH(1E+99,D:D)))*MOD(A2,$D$3:INDEX(D:D,MATCH(1E+99,D:D)))*$E$3:INDEX(E:E,MATCH(1E+99,D:D)))

Формула теперь динамична в том, что с увеличением или уменьшением таблицы будет увеличиваться и ссылка на набор данных. INDEX(D:D,MATCH(1E+99,D:D)) находит последнюю ячейку в столбце и устанавливает ее как экстент набора данных.

Answer 2

Это можно сделать с помощью if. Способ построения вложенного if - это делать то, что происходит с разными значениями. Если предположить, что столбец данных начинается с A2, то

1. A2 <10000. Результат a2 + 0 или A2
2. 10000 <A2 <12501 Результат - a2 + (. 25 * (A2-10000))
3. 12500 <A2 <15000 Результат - a2 + (. 25 * (a2-10000)) + (.25 * (A2-12500))

Чтобы преобразовать это в одну формулу, просто используйте if для каждого случая. Поток имеет вид = if(test, true, false), где, если test равен true, получить значение true, иначе получить значение false. Первое решение использует 3 ifs, ложный результат теста в нуле, истинные результаты в ответе

• = ЕСЛИ (А2 <10001, A2,0)+ ЕСЛИ (И (A2> 10000, A2 <12501), A2+ (0,25 * (A2-10000))+ Если (и (A2> 12500, А2 <15001), A2+ (0,25 * (A2-10000))+ (0,25 * (A2-12500))))

Это можно упростить, используя некоторую алгебру для

• = A2+ ЕСЛИ (А2> 10000, (0.25 * (A2-10000)), 0)+ ЕСЛИ (А2> 12500, (0.25 * (A2-12500)))

Любая форма может быть изменена, если вы хотите иметь дело с числами больше 15 000