Формула Excel для среднесуточной прибыли

Question

У меня есть таблица, которая имеет только два столбца, Дата и прибыль:

Date            Profit
24 - March      25.00
24 - March      19.85
25 - March      5.25
26 - March      6.36
26 - March      10.18
26 - March      35.63

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

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

В некоторые дни я не делаю никакой продажи; например, если я делаю продажи и получаю прибыль за 24 марта, 25 марта и 27 марта, я не сделал продажу 26 марта, я хочу, чтобы она рассчитывалась независимо от того, совершил ли я продажу на дату или нет. То есть в месяце, который имеет 31 день, он должен делиться на 31 независимо от того, совершил ли я продажу в конкретную дату или нет. Это будет возможно?

Answer 1

Для дат в ячейках A1:A25 и значений прибыли в ячейках B1:B25 можно использовать следующую формулу:

=SUM(B2:B25)/SUMPRODUCT(--(FREQUENCY(A2:A25,A2:A25)>0))

Результат как показано:

Когда у вас есть более 25 строк данных, вам нужно соответствующим образом настроить диапазоны формул.

Формула рассчитывается следующим образом:

(Total profit)/ (number of different dates)

РЕДАКТИРОВАТЬ

Согласно запросу, чтобы включить даты между (не указано), вы можете использовать что-то вроде этого:

=SUM($B$2:$B$25)/(MAX($A$2:$A$25)-MIN($A$2:$A$25)+1)

(Total profit)/ (days between smallest and largest date in range)

Answer 2

Прибыль можно суммировать с помощью =SUM(B2:B7) а количество дней можно рассчитать с помощью =TODAY()-MIN(A2:A7) что позволяет получить формулу, которая разделит эти два вида следующим образом:

=SUM(B2:B7)/(TODAY()-MIN(A2:A7))

Если в последнее время продажи не производились, формула будет давать результаты, которые со временем уменьшаются, поскольку она рассчитывается от даты первой продажи до текущей даты. замените TODAY() на MAX(A2:A7) чтобы изменить его так, чтобы он рассчитывал только от даты первой продажи до даты последней продажи.

Answer 3

Один из способов сделать это - создать третий скрытый столбец для вычисления среднего значения для каждой даты, а затем усреднить этот столбец. Введите следующую формулу в C2, а затем скопируйте и вставьте ее полностью или, по крайней мере, достаточно далеко, чтобы охватить будущие записи. Если вы сделаете эту таблицу, а столбец C - вычисляемым столбцом, то он будет автоматически введен в столбец C любых новых строк при расширении таблицы.

=IF(OR(COUNTIF(A$2:A2, A2) > 1, NOT(ISNUMBER(A2))), "", AVERAGEIF(A:A, A2, B:B))

Сначала проверяется, впервые ли дата появилась на листе, используя функцию COUNTIF в столбце A, но только до текущей ячейки. Он также проверяет, что столбец A является числом с функциями NOT и ISNUMBER поэтому он не возвращает ошибку (Excel обрабатывает даты как числа.) Затем он использует функцию AVERAGEIF для усреднения всех элементов в столбце B, имеющих совпадающие даты.

Это также позволяет вводить даты в любом порядке и учитывает пробелы.

Теперь в другой ячейке просто усредните столбец с помощью =AVERAGE(C:C) чтобы получить среднюю дневную прибыль.

Затем вы можете скрыть столбец C, щелкнув его правой кнопкой мыши и щелкнув Скрыть. Вы можете отобразить его, когда вам нужно, выбрав столбцы с B по D и щелкнув правой кнопкой мыши, а затем нажав Показать.