Рассчитать среднее количество дней между двумя датами для уникальных полей

Question

Мне нужна помощь с единственной формулой в поиске уникальных задач, а затем в среднем между двумя датами. Глядя на приведенный ниже пример:

---

Задание закрыто Создано
Задача1 13.03.2008 1/1/2018
Задача1 13.03.2008 1/1/2018
Задача1 13.03.2008 1/1/2018
Задача2 13.03.2008 01.02.2008
Задача2 13.03.2008 01.02.2008
Задача 3 13.03.2008 3/1/2018

---

Мне нужно взять задание 1 и вычесть 13.03.2008 из 01.01.08, чтобы получить 71
Мне нужно взять Task2 и вычесть 13.03.2008 из 01.02.2008, чтобы получить 40
Мне нужно взять задание 3 и вычесть 13.03.08 из 01.03.2008, чтобы получить 12
Это дает мне средний ответ 41

---

Есть ли формула, которую я могу использовать, чтобы получить ответ 41?
Я попытался {= AVERAGE(B2:B6 - C2:C7)}, но я не знаю, как сначала проверить, уникальна ли она. Что-то вроде {= Если A2:A7 уникален, СРЕДНИЙ (B2:B6 - C2:C7)}

Чтобы усложнить ситуацию дальше, бывают случаи, когда в столбце B не будет значений (Закрыто)

Answer 1

Вы можете использовать эту формулу в соседнем столбце для подсчета вхождений каждой задачи:

=COUNTIF(A$1:A1,A1)

Вы должны поместить это в первый ряд и перетащить, чтобы заполнить его вниз по столбцу. Это заставляет его транспонировать правильно для получения желаемого результата. Формула подсчитывает, сколько строк до текущей строки содержат одно и то же значение:

Task1   1
Task1   2   
Task2   1
Task3   1
Task1   3   
Task4   1
Task2   2   
Task3   2

Затем используйте другую формулу в новом столбце, чтобы зафиксировать разницу в датах, но только в том случае, если число вхождений равно 1:

=IF(D1=1,B1-C1,"")

Task1   1/01/2011   1/01/2010   1   365
Task1   1/01/2011   1/01/2010   2   
Task2   4/01/2011   1/04/2010   1   278
Task3   6/01/2011   1/06/2010   1   219
Task1   1/01/2011   1/01/2010   3   
Task4   10/01/2011  1/10/2010   1   101
Task2   4/01/2011   1/04/2010   2   
Task3   6/01/2011   1/06/2010   2   

Затем усредните последний столбец.

Конечно, вы можете объединить две вышеупомянутые формулы - я просто предоставил обе для объяснения.

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

Вам нужно решить, что вы хотите сделать с пустым столбцом B. Например, вы можете игнорировать незакрытые задачи в формуле разницы дат следующим образом:

=IF(AND(NOT(ISBLANK(B4)),D4=1),B4-C4,"")