Бросающая формула в 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

    1. Решения для 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

    1. Решение для 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

    Interesting Posts

    Excel – как создать зависимый выпадающий список, который использует разные уровни?

    ранжирование нескольких списков текста на основе позиции в excel

    Копирование изображения с помощью рабочего листа с использованием макроса

    Перемешать значения в новую строку по две ячейки за раз

    Импорт текстовых файлов с пустой строкой?

    Использование числа в ячейке для создания ссылки на ячейку

    Экспорт таблицы HTML в excel с помощью javascript

    Может ли столбец или граф Excel отображать отсортированную версию связанного столбца?

    Возвратите наименьшие два числа с негативами в Excel

    Ошибка «без модуля с именем xlwings» в Windows при вызове сценария Python в файле XLSM

    Автоматическая отправка электронной почты в VBA 2013

    Hyperlinks.Follow error: Ошибка во время выполнения '9': подкатегория вне диапазона

    Эксклюзивный выпуск VBA

    получение имен подключений от excel vba

    Ошибка с функцией Format

    Давайте будем гением компьютера.