Calculating the average handling time to complete a task

[lorrry]

Hi,

I'm stuck with a particular kind of formula in excel. I'm looking to calculate the average amount of days it takes for a transaction to be completed for each month. I have a sheet listing when all the expressions of interest and the date/time that this occurred and another listing when the transaction was completed with the date/time. Some expressions of interests never pan out, therefore don't appear in the completed transactions.

For example, I have the data

Expression of interest:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Business1[/TD]
[TD]4/4/2019[/TD]
[/TR]
[TR]
[TD]Business2[/TD]
[TD]4/16/2019[/TD]
[/TR]
[TR]
[TD]Business3[/TD]
[TD]4/25/2019[/TD]
[/TR]
[TR]
[TD]Business4[/TD]
[TD]5/1/2019[/TD]
[/TR]
[TR]
[TD]Business5[/TD]
[TD]5/3/2019[/TD]
[/TR]
</tbody>[/TABLE]

Completed transaction:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Business2[/TD]
[TD]4/20/2019[/TD]
[/TR]
[TR]
[TD]Business3[/TD]
[TD]4/27/2019
[/TD]
[/TR]
[TR]
[TD]Business4[/TD]
[TD]5/3/2019[/TD]
[/TR]
</tbody>[/TABLE]

Desired data:
[TABLE="width: 500"]
<tbody>[TR]
[TD]April 2019[/TD]
[TD]=Average days taken to complete transaction in April 2019[/TD]
[/TR]
[TR]
[TD]May 2019[/TD]
[TD]=Average days taken to complete transaction in May 2019[/TD]
[/TR]
</tbody>[/TABLE]


Does anyone have any guidance of how I should approach this?

 

[AlanY]

note that I'd swapped day/month from your data for testing

Book1
	A	B	C	D	E	F	G	H
1	Expression of interest:			Completed transaction:				Average
2	Business1	04/04/2019		Business2	20/04/2019		Apr-19	3
3	Business2	16/04/2019		Business3	27/04/2019		May-19	2
4	Business3	25/04/2019		Business4	03/05/2019
5	Business4	01/05/2019
6	Business5	03/05/2019
Sheet2

Cell Formulas
Range	Formula
H2	=(SUMPRODUCT($E$2:$E$4,--(MONTH($E$2:$E$4)&YEAR($E$2:$E$4)=MONTH($G2)&YEAR($G2)))-SUMPRODUCT($B$2:$B$6,--(ISNUMBER(MATCH($A$2:$A$6,D:D,0))),--(MONTH($B$2:$B$6)&YEAR($B$2:$B$6)=MONTH($G2)&YEAR($G2))))/SUMPRODUCT(--(MONTH($E$2:$E$4)&YEAR($E$2:$E$4)=MONTH($G2)&YEAR($G2)))

 

[levy77]

I took a slightly different approach but very similar to above. Mine just adds one extra column than above.

https://drive.google.com/open?id=1n0UoQQLi6FqAbCE9S67nn-n3um18cQKe

Any feedback would be much appreciated.

 

[levy77]

I have just had a play around with this formula and it is a great way of going about the problem. However, I could not get it to calculate the days between if the EOI was the previous month to the completed transaction. E.g. EOI on 1/7/2019 and Completed Transaction on 1/8/2019. The formula will not calculate this.

 

[lorrry]

Wow!

Thanks so much for responding so quickly guys. Really appreciate your help. I'll apply both of these formulas and see what works best.