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

 

[duggie33]

I am unsure about simplicity, but I think I found a way. There are likely other ways that are more elegant...LOL. It seems to work and there is no need for the "today" calculation at the bottom of the data.

Book1
	A	B	C	D	E
1	Date	Means		Average
2	1/6/2023	A		A	49
3	1/9/2023	B		B	121
4	1/12/2023	A		C	101
5	1/30/2023	A
6	2/5/2023	B
7	2/18/2023	C
8	3/15/2023	A
9	4/1/2023	A
10	4/9/2023	C
Sheet2

Cell Formulas
Range	Formula
E2:E4	E2	=LET(filt,SORT(FILTER($A$2:$A$10,$B$2:$B$10=$D2)),filt_dates,VSTACK(filt,TODAY()),cnt,COUNT(filt_dates),seq_lg,SEQUENCE(cnt-1,,2),seq_sm,SEQUENCE(cnt-1,,1),AVERAGE(SMALL(filt_dates,seq_lg)-SMALL(filt_dates,seq_sm)))

Hope that helps,

Doug

 

[ItalianPlatinum]

Seems to not be working for me

 

[duggie33]

Are you using Excel version 365?

 

[Phuoc]

Hi,

What is the expected result in F2:F4?

 

[ItalianPlatinum]

Are you using Excel version 365?

Office 2019 let me update my profile just realized it lists 365

 

[ItalianPlatinum]

Hi,

What is the expected result in F2:F4?

Something like that hope i didnt fat finger one of the figures

Book4.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Date	Means	Elapsed time		Average	27.22222222		A	B	C
2	6-Jan	A			A	FORMULA NEEDED		6	27	50
3	9-Jan	B	3		B	FORMULA NEEDED		18
4	12-Jan	A	3		C	FORMULA NEEDED		44
5	30-Jan	A	18					17
6	5-Feb	B	6				elapsed	160	215	152
7	18-Feb	C	13				Average	49	121	101
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)
I2	I2	=A6-A3
J2	J2	=A10-A7
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]

Try this:

Book1
	B	C	D	E
1	Means		Average
2	A		A	49.20
3	B		B	121.50
4	A		C	101.50
5	A
6	B
7	C
8	A
9	A
10	C
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=AVERAGE(AGGREGATE(14,6,$A$2:$A$10/($B$2:$B$10=D2),SEQUENCE(COUNTIF($B$2:$B$10,D2)-1))-AGGREGATE(14,6,$A$2:$A$10/($B$2:$B$10=D2),SEQUENCE(COUNTIF($B$2:$B$10,D2)-1)+1),TODAY()-AGGREGATE(14,6,$A$2:$A$10/($B$2:$B$10=D2),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Phuoc]

Or

Book1
	B	C	D	E
1	Means		Average
2	A		A	49.20
3	B		B	121.50
4	A		C	101.50
5	A
6	B
7	C
8	A
9	A
10	C
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=AVERAGE(AGGREGATE(14,6,$A$2:$A$10/($B$2:$B$10=D2),ROW(INDIRECT("1:"&COUNTIF($B$2:$B$10,D2)-1))) -AGGREGATE(14,6,$A$2:$A$10/($B$2:$B$10=D2),ROW(INDIRECT("2:"&COUNTIF($B$2:$B$10,D2)))), TODAY()-AGGREGATE(14,6,$A$2:$A$10/($B$2:$B$10=D2),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ItalianPlatinum]

Do you know if that formula is compatitlbe with google sheets?