Formulas needed average

[ItalianPlatinum]

Hello - I am looking to determine an average between dates based off of criteria. i thought i could do it simply but running into a roadblock. The elapsed time is just from the previous occurence with the last record from the last record to today. I dont want to add any other columns i would like to keep this simplistic so reaching out for help if there was a way i could still accomplish this. When i add a new record i move the today formula down and drag the other (prev - current) down 1. Thanks in advance.

Book4.xlsx
	A	B	C	D	E	F
1	Date	Means	Elapsed time		Average	27.22222222
2	6-Jan	A			Average A	FORMULA NEEDED
3	9-Jan	B	3		Average B	FORMULA NEEDED
4	12-Jan	A	3		Average C	FORMULA NEEDED
5	30-Jan	A	18
6	5-Feb	B	6
7	18-Feb	C	13
8	15-Mar	A	25
9	1-Apr	A	17
10	9-Apr	C	8
11			152
Sheet1

Cell Formulas
Range	Formula
F1	F1	=AVERAGE(C:C)
C3:C10	C3	=A3-A2
C11	C11	=TODAY()-A10

 

[Phuoc]

No, Google Sheets does not support the AGGREGATE function, change to:

=AVERAGE(LARGE(IF($B$2:$B$10=D2,$A$2:$A$10,""),ROW(INDIRECT("1:"&COUNTIF($B$2:$B$10,D2)-1))) 
-LARGE(IF($B$2:$B$10=D2,$A$2:$A$10,""),ROW(INDIRECT("2:"&COUNTIF($B$2:$B$10,D2)))), 
TODAY()-LARGE(IF($B$2:$B$10=D2,$A$2:$A$10,""),1))

 

[ItalianPlatinum]

Doesn't seem to be yielding results did it work for you?

Book4.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Date	Means	Elapsed time		Average	27.33333333		A	B	C
2	1/6/2023	A			A	80.5		6	27	50
3	1/9/2023	B	3		B	80.5		18
4	1/12/2023	A	3		C	#VALUE!		44
5	1/30/2023	A	18					17
6	2/5/2023	B	6				elapsed	161	216	153
7	2/18/2023	C	13				Average	49	122	102
8	3/15/2023	A	25
9	4/1/2023	A	17
10	4/9/2023	C	8
11			153
Sheet2

Cell Formulas
Range	Formula
I2	I2	=A6-A3
J2	J2	=A10-A7
F1	F1	=AVERAGE(C:C)
F2:F4	F2	=AVERAGE(LARGE(IF($B:$B=E2,$A:$A,""),ROW(INDIRECT("1:"&COUNTIF($B:$B,E2)-1))) -LARGE(IF($B:$B=E2,$A:$A,""),ROW(INDIRECT("2:"&COUNTIF($B:$B,E2)))), TODAY()-LARGE(IF($B:$B=E2,$A:$A,""),1))
H2	H2	=A4-A2
H3	H3	=A5-A4
H4	H4	=A8-A5
H5	H5	=A9-A8
H6	H6	=TODAY()-A9
I6	I6	=TODAY()-A6
J6	J6	=TODAY()-A10
H7:J7	H7	=AVERAGE(H2:H6)
C3:C10	C3	=A3-A2
C11	C11	=TODAY()-A10

 

[Phuoc]

Confirm with Ctrl+Shift+Enter.

 

[ItalianPlatinum]

Ok thanks works in excel will test google sheets

 

[ItalianPlatinum]

No, Google Sheets does not support the AGGREGATE function, change to:

=AVERAGE(LARGE(IF($B$2:$B$10=D2,$A$2:$A$10,""),ROW(INDIRECT("1:"&COUNTIF($B$2:$B$10,D2)-1)))
-LARGE(IF($B$2:$B$10=D2,$A$2:$A$10,""),ROW(INDIRECT("2:"&COUNTIF($B$2:$B$10,D2)))),
TODAY()-LARGE(IF($B$2:$B$10=D2,$A$2:$A$10,""),1))

I noticed while expanding the formula to my larger dataset it doesn't seem to do an average based off of the elapsed time (row 11). for example i put 1,000 days in there and the averages based off of criteria (A,B,C) didn't change

 

[ItalianPlatinum]

actually i think this part of the formula would cover it so i should be ok the test i was doing i just added +1000 in there and the formula was correct

TODAY()-LARGE(IF($B$2:$B$10=D2,$A$2:$A$10,""),1))