Average a date difference based on criteria

[GergelySzakacs]

Hi Team,

I am having difficulty with the below problem. I need to average the day differences between two dates, based on other criteria.

Here is the sample spreadsheet:

So, I would need the average date difference between B and A colums for every A, B, and C categories. So, one day number for A, B and C categories.

I spend so many times already on this, I hope you guys can help me

Thanks,
Gergely

 

[AlanY]

have you tried the =AVERAGEIF() function?

 

[GergelySzakacs]

Yes, I tried, but I got confused after a while. I am confused because of the range. For one line I can understand average if but not for a range. Can you help me how to do that?

 

[AlanY]

it's much easier with a helper column D

Book1
	A	B	C	D	E
1	31/01/2022	07/02/2022	A	7	8.5
2	31/01/2022	08/02/2022	B	8	9.5
3	31/01/2022	09/02/2022	C	9	9
4	31/01/2022	10/02/2022	A	10
5	31/01/2022	11/02/2022	B	11
Sheet1

Cell Formulas
Range	Formula
E1:E3	E1	=AVERAGEIF($C$1:$C$5,C1,$D$1:$D$5)
D1:D5	D1	=B1-A1

 

[GergelySzakacs]

I know, but I would avoid any extra column. The source is a spreadsheet that is edited by a lot of people, so I want to keep it as simple as it can be.

 

[AlanY]

ok, try this

Book1
	A	B	C	D
1	31/01/2022	07/02/2022	A	8.5
2	31/01/2022	08/02/2022	B	9.5
3	31/01/2022	09/02/2022	C	9
4	31/01/2022	10/02/2022	A
5	31/01/2022	11/02/2022	B
Sheet1

Cell Formulas
Range	Formula
D1:D3	D1	=SUMPRODUCT(--(($C$1:$C$5=C1)*($B$1:$B$5-$A$1:$A$5)))/COUNTIF($C$1:$C$5,C1)