Бросающая формула в excel для вычисления последовательного остатка
Я пытаюсь найти формулу, в которой будет рассчитана сумма резерва, в зависимости от времени, которое было принято для завершения задачи.
Вот пример, в ячейке:
A1: У меня есть значение на 150 (которое не меняется)
B1: У меня есть время, которое было выполнено для выполнения задачи
C1: Я хочу, чтобы формула возвращала значение на основе критериев ниже
Критерии 1: 6 часов или менее возврат 20% от нормы = IF (AND (B1> = 0,01, B1 <= 5,99), A1 * 0,2, "")
Критерии 2: между 6 и 12 часами возвращают 50% от нормы = IF (AND (B1> = 6, B1 <= 11.99), A1 * 0.5, "")
Критерии 3: В период между 12 и 24 часами возвращают 100% от нормы = IF (AND (B1> = 12, B1 <= 23.99), A1 * 1, "")
Критерии 4: Первые 24 часа, 100% надбавки и за каждые последующие 12 часов добавляют 50% надбавки (на 100% за первые 24 часа)
Если возможно, я хотел бы, чтобы одна формула охватывала все эти критерии в ячейке C1. Как вы можете видеть, у меня есть формула для первых трех критериев, но не последняя.
Было бы здорово, если кто-то может помочь. Спасибо заранее 🙂
Ab
Я заменил условие FALSE в каждом выражении IF следующими критериями. Если первые три критерия FALSE, предполагается, что вы более 24 часов и выполняете критерии 4.
= IF (AND (B1> = 0.01, B1 <= 5.99), A1 * 0.2, IF (AND (B1> = 6, B1 <= 11.99), A1 * 0.5, IF (AND (B1> = 12, B1 < = 23,99), A1 * 1, A1 * 1 + A1 * ROUNDUP ((B1-24) / 12,0) / 2))))
Со временем, прошедшим столбец A, поместите эту формулу в столбец B.
=(LOOKUP(A2*24, {0,0.1,6,12}, {0,0.2,0.5,1})+CEILING(MAX(A2-1.5, 0), 0.5))*150 Заполните, если необходимо. Я старался изо всех сил изолировать $ 150.00. Это должно быть легко помещать в ячейку (например, Z1) и ссылаться на нее вместо жесткого кодирования суммы в (например ...*$Z$1 )
Эскалационное распределение по времени
Я публикую эти решения в интересах пользователей, которые могут ссылаться на этот вопрос.
Давайте сначала проверим критерии, заданные в вопросе, так как некоторые несоответствия между описанием критериев и формулами, приведенными в качестве приоритета, даются формулам, поскольку было подтверждено, что они верны .
рисунок 1
Хотелось бы проверить \ выделить три точки из таблицы:
я. Минимальная ставка вознаграждения составляет 20%, как только будет указано время (т.е. Time > 0 )
II. Если Time > 24 часа и Time < 36 часов, то ставка надбавки составляет 100%
III. Если Time >= 48 часов и Time > 60 часов, ставка составляет 200% (100% в первые 24 часа + 50% каждые 12 часов)
Хотя, когда его спрашивают
являются ли времена, которые будут использоваться в качестве критериев, фактические времена или целые числа и смешанные числа, представляющие часы
пользователь «подтвердил», что
«Есть фактические времена»
Похоже, что это не так, потому что пользователь позже признает правильную формулу, которая не может предоставить правильные результаты, если время выражается как «фактическое время» . На рисунке 2 показано время, выраженное в обоих направлениях.
Рисунок 2
Решения
Ниже вы найдете формулы для каждого формата.
Различные ссылки, указанные в примере, используются для того, чтобы показать оба способа выражения Time и их решений. При необходимости измените ссылки.
Предполагая, что базовая сумма разрешений 150 находится в C2
- Решения для
Timeвыраженные какDouble(см. Рис.3)
а. Вложенная формула IF : с Time в C8 введите эту формулу в E8
=$C$2 * SUM( IF( $C8 <= 0, 0, IF( $C8 < 6, 20%, IF( $C8 < 12, 50%, 100% ))), MAX( INT( $C8 / 12 ) - 2, 0 ) * 50% )
б. Формула, основанная на решении Jeeped: с Time в C9 введите эту формулу в E9
=$C$2 * ( LOOKUP( ROUND( $C9, 2 ), {0,0;0.01,0.2;6,0.5;12,1} ) + CEILING( MAX( ( $C9 / 24 ) - 1.5 + 0.0001, 0 ), 0.5 ))
Рисунок 3
- Решение для
Timeвыраженное какActual Time(см. Рисунок 4)
(изменение от 1 означает, что Time умножается на 24, чтобы преобразовать его как двойное)
а. Вложенная формула IF : со Time в C13 введите эту формулу в E13
=$C$2 * SUM( IF( $C13 * 24 <= 0, 0, IF( $C13 * 24 < 6, 20%, IF( $C13 * 24 < 12, 50%, 100% ))), MAX( INT( $C13 * 24 / 12 ) - 2, 0 ) * 50% )
б. Формула, основанная на решении Jeeped: с Time в C14 введите эту формулу в E14
=$C$2 * ( LOOKUP( ROUND( $C14 * 24, 2), {0,0;0.01,0.2;6,0.5;12,1}) + CEILING( MAX( $C14 - 1.5 + 0.0001, 0 ), 0.5 ))
Рисунок 4