Averaging the difference between two dates and separating them into categories

[Bpc1284]

I am trying to create a table that will produce the average difference between start dates and completion dates for projects therefore giving average completion time in days for projects. I would also like this to be separated by the category of project it is which is located in column E of my sheet. I have used an AVERAGE(FILTER(DAYS( function but this only provides the average completion time for all categories of projects combined. I believe an AVERAGEIFS is what I need to use but I can’t figure it out.

My current formula is
=AVERAGE(FILTER(DAYS($G$2:$G$100000,$C$2:$C$10000), $G$2:$G$10000<>””))

Start dates are in column C, Completion dates are in column G, and criteria I want to categorize by is in column E.

 

[awoohaw]

Welcome to the forum!

try this:

=AVERAGE(FILTER(($G$3:$G$100000-$C$3:$C$100000),($e$3:$e$100000=E3)))

Book1
	A	B	C	D	E	F	G
1
2			Start		Project Type		End
3			2023-09-01		A		2024-03-10
4			2023-01-01		B		2024-01-10
5			2023-04-01		A		2024-05-10
6			2023-04-01		B		2024-01-10
7			2023-04-01		C		2024-03-10
8			2023-01-01		D		2024-03-10
9			2023-04-01		B		2024-05-10
10			2023-04-01		B		2024-01-10
11			2023-09-01		A		2024-03-10
12
13	A	262.3333333
14	B	336.75
15	C	344
16	D	434
Sheet1

Cell Formulas
Range	Formula
A13:A16	A13	=UNIQUE(E3:E11)
B13:B16	B13	=AVERAGE(FILTER(($G$3:$G$11-$C$3:$C$11),($E$3:$E$11=A13)))
Dynamic array formulas.

 

[Bpc1284]

Thank you for the quick reply! When I try your solution I end up with a large negative number. Possibly because it does not remove rows below that have not been filled out yet? Is there any way to make provisions to not include the empty rows as well as the rows with a start date but no completed date?

 

[awoohaw]

Thank you for the quick reply! When I try your solution I end up with a large negative number. Possibly because it does not remove rows below that have not been filled out yet? Is there any way to make provisions to not include the empty rows as well as the rows with a start date but no completed date?

It only filters records with column E equal to the target project type, that is what you asked. .

This will filter out records with blank end dates:

=AVERAGE(FILTER(($G$3:$G$10000-$C$3:$C$10000),(($E$3:$E$10000=A22)*($G$3:$G$10000>0))))

Book1
	A	B	C	D	E	F	G
1
2			Start		Project Type		End
3			2023-09-01		A		2024-03-10
4			2023-01-01		B		2024-01-10
5			2023-04-01		A		2024-05-10
6			2023-04-01		B		2024-01-10
7			2023-04-01		C		2024-03-10
8			2023-01-01		D		2024-03-10
9			2023-04-01		B		2024-05-10
10			2023-04-01		B		2024-01-10
11			2023-09-01		A		2024-03-10
12			2023-04-01		D
13			2023-04-01		B
14			2023-01-01		B
15			2023-04-01		A
16			2023-04-01		B
17			2023-09-01		A
18
19
20
21
22	A	262.333333
23	B	336.75
24	C	344
25	D	434
26
Sheet1

Cell Formulas
Range	Formula
A22:A25	A22	=UNIQUE(E3:E11)
B22:B25	B22	=AVERAGE(FILTER(($G$3:$G$17-$C$3:$C$17),(($E$3:$E$17=A22)*($G$3:$G$17>0))))
Dynamic array formulas.

 

[Bpc1284]

Thank you!

 

[awoohaw]

Thank you!

You're welcome, happy to help!
Best Wishes!