Average time between dates *But ignore if blank in either cell

[DaveMacdonald]

Version: Excel Online

I want an average of the time (in days) between two milestones for many different people (one person per row), but I want the formula to ignore that row if there isn't a date in BOTH date cells, since it throws off the average in a major way.

Example "TABLE_1"

EE Name	MILESTONE 1	MILESTONE 2
Dave	4/12/22	5/12/22
Steve	6/1/22	6/5/22
Bill	7/22/22	7/26/22
Frank	8/1/22

Current Formula =SUMPRODUCT(TABLE_1[MILESTONE 2]-TABLE_1[MILESTONE 1])/COUNT(TABLE_1[MILESTONE 2])&" Days"

As long as both milestone 1 and 2 are populated, the formula works great, but if milestone 1 is populated and milestone 2 isn't, then it throws the whole thing off. How would I write a formula where if row 5 as above has a blank, then neither columns 2 or 3 for that row are part of the average calculation?

 

[Eric W]

Try:

Book1
	A	B	C	D	E
1	EE Name	MILESTONE 1	MILESTONE 2
2	Dave	4/12/2022	5/12/2022		12.66667
3	Steve	6/1/2022	6/5/2022
4	Bill	7/22/2022	7/26/2022
5	Frank	8/1/2022
6
Table 1

Cell Formulas
Range	Formula
E2	E2	=LET(a,FILTER(B2:C7,(B2:B7<>"")*(C2:C7<>"")),SUM(a*{-1,1})/ROWS(a))

 

[Eric W]

I just noticed you're using table nomenclature. Try:

=LET(a,Table_1[[MILESTONE 1]:[MILESTONE 2]],f,FILTER(a,MMULT(--(a<>""),{1;1})=2),TEXT(SUM(f*{-1,1})/ROWS(f),"0.00"))&" days"

 

[DaveMacdonald]

I just noticed you're using table nomenclature. Try:

=LET(a,Table_1[[MILESTONE 1]:[MILESTONE 2]],f,FILTER(a,MMULT(--(a<>""),{1;1})=2),TEXT(SUM(f*{-1,1})/ROWS(f),"0.00"))&" days"

Thanks for your reply! Unfortunately I get a #VALUE! error. It is probably pertinent to the formula, Milestones 1 and 2 are not side by side, So i dont think milestone 1:milestone 2 will work. Is there a workaround for that?

 

[Eric W]

I wondered about that, I should have mentioned it. The easiest fix I can think of is to add another filter. For example:

Book1
	A	B	C	D	E	F	G
1	EE Name	MILESTONE 1	Something	Something Else	MILESTONE 2
2	Dave	4/12/2022	aa		5/12/2022		12.67 days
3	Steve	6/1/2022	b		6/5/2022
4	Bill	7/22/2022	c		7/26/2022
5	Frank	8/1/2022	d
Table 1

Cell Formulas
Range	Formula
G2	G2	=LET(a,FILTER(Table_1[[MILESTONE 1]:[MILESTONE 2]],{1,0,0,1}),f,FILTER(a,MMULT(--(a<>""),{1;1})=2),TEXT(SUM(f*{-1,1})/ROWS(f),"0.00"))&" days"

Look for the {1,0,0,1} array near the start of the formula. This says that there are 2 columns (the zeros) between the milestone columns. Once those are filtered out, the rest works ok.

 

[DaveMacdonald]

I wondered about that, I should have mentioned it. The easiest fix I can think of is to add another filter. For example:

Book1
	A	B	C	D	E	F	G
1	EE Name	MILESTONE 1	Something	Something Else	MILESTONE 2
2	Dave	4/12/2022	aa		5/12/2022		12.67 days
3	Steve	6/1/2022	b		6/5/2022
4	Bill	7/22/2022	c		7/26/2022
5	Frank	8/1/2022	d
Table 1

Cell Formulas
Range	Formula
G2	G2	=LET(a,FILTER(Table_1[[MILESTONE 1]:[MILESTONE 2]],{1,0,0,1}),f,FILTER(a,MMULT(--(a<>""),{1;1})=2),TEXT(SUM(f*{-1,1})/ROWS(f),"0.00"))&" days"

Look for the {1,0,0,1} array near the start of the formula. This says that there are 2 columns (the zeros) between the milestone columns. Once those are filtered out, the rest works ok.

You're clearly a wizard because that worked like magic! (too corny?) Really though that's it exactly, thank you so much!

 

[Eric W]

Glad it works for you!