Блог

Я пытаюсь определить переменные безубыточности для различных столбцов на основе следующей таблицы.

• Местоположения имеют заданное количество устройств (столбец B) и транзакций (столбец C), которые происходят.
• Я хотел бы вычислить формулу для столбцов M-Q, которая показывала бы безубыточность для каждого из этих столбцов.

Я внес следующие изменения, чтобы соответствовать этим конкретным столбцам для «Местоположения 1» в качестве примера:

• Вычисленное значение M3 путем обновления ячейки C3 до тех пор, пока E3 (переменная) не совпадет с J3 (статическим).
• Вычисленное значение N3 путем обновления ячейки C3 до тех пор, пока F3 (переменная) не совпадет с K3 (статическим).
• Вычисленное значение O3 путем обновления ячейки B3 до тех пор, пока J3 (переменная) не совпадет с E3 (статическим).
• Вычисленное значение P3 путем обновления ячейки B22 до тех пор, пока J3 (переменная) не совпадет с E3 (статическим).
• Не удалось найти простой способ выяснить, сколько лет потребуется для сопоставления модели полосы движения (статической) с моделью для каждой страны (переменной).

Я бы хотел, чтобы лист был динамическим, что означает, что если я настрою любое из переменных полей B3: B18, C3: C18 или B22: B24, я получу значения безубыточности в столбцах M-Q для автоматического обновления.

Я думаю, что у меня есть часть ответа для вас — все еще работаю над остальным. Попробуйте эти две формулы в M3 и N3.

Безубыточные транзакции в М3

 =ArrayFormula((H3:H I3:I)/$B$23)
 

Безубыточные транзакции 5 лет в N3

 =ArrayFormula(((H3:H (5*I3:I))/5)/$B$23)
 

Обновить

Джо, я думаю, у меня есть оставшиеся три формулы, которые тебе нужны. Пожалуйста, проверьте результаты, чтобы убедиться, что они имеют смысл, но я считаю, что у меня есть правильная логика в формулах.

Также обратите внимание, что, чтобы помочь мне следовать логике, я использовал именованные диапазоны для трех переменных, которые в настоящее время находятся в ячейках B22, B23 и B24. Я считаю, что это увеличивает вероятность ошибок, если я смешиваю истинные данные с различными типами переменных или констант, поэтому я переместил их вправо в моем примере листа. Ваш B22 я назвал PerLicenseCost, B23 — PerTransCost, а B24 — MaintRate .

Новые формулы выглядят следующим образом:

Количество устройств безубыточности

 =ArrayFormula(ROUNDUP(((C3:C*PerTransCost)/(1 MaintRate))/PerLicenseCost))
 

Среднее значение безубыточности. Лицензионный сбор

 =ArrayFormula(((C3:C*PerTransCost)/(1   MaintRate))/B3:B)
 

Годы безубыточности

 = ArrayFormula(-1 *  (B3:B * PerLicenseCost) / ((B3:B * PerLicenseCost * MaintRate) - (C3:C * PerTransCost)))
 

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

За годы безубыточности было зафиксировано четыре отрицательных результата. Я надеюсь, это означает, что эти заведения теряют деньги и никогда не будут безубыточными, но я позволю вам просмотреть их и принять решение.

Обратите также внимание, что вы можете легко изменить эти формулы, чтобы переместить их вверх в строку заголовка (строка 2), если хотите. Для этого в заголовке нужного столбца скопируйте существующую формулу из ячейки ниже БЕЗ знака равенства и вставьте в следующую:

 ={“ text header for the column “;    PASTE CURRENT FORMULA HERE   } 
 

Затем обязательно удалите формулу из ячейки ниже (в строке 3), иначе у вас появится ошибка #REF. Если вам понадобится какая-либо помощь в этом, дайте мне знать.

Дайте мне знать, если у вас возникнут какие-либо вопросы по этому поводу. Я буду рад ответить на них, но, возможно, не раньше, чем через день или два.