Dynamic Formula to extract data before and after dates

[DRSteele]

Can someone please help me?

I am looking to create a formula using function FILTER to extract data using dates as criteria. As you can see, it is not as easy as expected. The problems arise when the Start Month is a higher value than the End Month because it wraps around the year and when the Start Day is a higher value than the End Day because it wraps around a month.

I want to use function FILTER to extract and report the filtered Dates and the average of Value for each of the four examples.

[The formula in F10 obviously goes all the way down. I did not paste all the data here.]

MrExcel posts18.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2									Example1			Example2			Example3			Example4
3									Start	End		Start	End		Start	End		Start	End
4								Month	2	3		4	5		11	1		10	2
5	Date	Month	Day	Year	Value			Day	1	28		20	7		6	16		23	9
6	4/11/2010	4	11	2010	-10			Year	2019	2020		2005	2009		2017	2020		2019	2020
7	4/12/2010	4	12	2010	2			Date	2/1/2019	3/28/2020		4/20/2005	5/7/2009		11/6/2017	1/16/2020		10/23/2019	2/9/2020
8	4/13/2010	4	13	2010	-4
9	4/14/2010	4	14	2010	-9				Date	Average Value		Date	Average Value		Date	Average Value		Date	Average Value
10	4/15/2010	4	15	2010	0				need dynamic formulas			need dynamic formulas			need dynamic formulas			need dynamic formulas
Sheet11

Cell Formulas
Range	Formula
B6:B3655	B6	=SEQUENCE(3650,,40279)
C6:C3655	C6	=MONTH(B6#)
D6:D3655	D6	=DAY(B6#)
E6:E3655	E6	=YEAR(B6#)
J7:K7,S7:T7,P7:Q7,M7:N7	J7	=DATE(J6,J4,J5)
F6:F10	F6	=RANDBETWEEN(-10,10)
Dynamic array formulas.

 

[StephenCrump]

I'm not clear what the month/day problem is?

Isn't example 1 simply: =AVERAGE(FILTER(F6:F3655,(B6#>=J7)*(B6#<=K7)))

 

[KRice]

I'm not clear on the issue either. I don't have FILTER available, but unless I am missing something, a solution with AVERAGEIFS looks like:
=IFERROR(AVERAGEIFS($F$6:$F$10,$B$6:$B$10,">="&J7,$B$6:$B$10,"<="&K7),"")

 

[Fluff]

maybe

+Fluff.xlsm
	B	C	D	E	F	G	H	I	J	K
1
2									Example1
3									Start	End
4								Month	2	3
5	Date	Month	Day	Year	Value			Day	1	28
6	11/04/2010	4	11	2010	3.825262712			Year	2019	2020
7	12/04/2010	4	12	2010	-2.643838346			Date	01/02/2019	28/03/2020
8	13/04/2010	4	13	2010	9.95258901
9	14/04/2010	4	14	2010	3.975197492				Date	Average Value
10	15/04/2010	4	15	2010	5.928742595				need dynamic formulas	-0.063955777
11	16/04/2010	4	16	2010	-2.250015218
12	17/04/2010	4	17	2010	6.249622423
13	18/04/2010	4	18	2010	-4.73278813
List

Cell Formulas
Range	Formula
B6:B3655	B6	=SEQUENCE(3650,,40279)
C6:C3655	C6	=MONTH(B6#)
D6:D3655	D6	=DAY(B6#)
E6:E3655	E6	=YEAR(B6#)
F6:F3655	F6	=RANDARRAY(ROWS(B6#),1,-10,10)
J7:K7	J7	=DATE(J6,J4,J5)
K10	K10	=AVERAGE(FILTER(F6#,(B6#>=J7)*(B6#<=K7)))
Dynamic array formulas.

 

[DRSteele]

Thank you for your responses Stephen, Fluff and Rice.

I made a mistake by including that DATE formula in row7 for each example. Many apologies! I should have been clearer.

What I want is the average value for a portion of a year across certain years. In Eg3, I want this period to be Nov1 to Jun16, but only for the years 2017-20. I'm not after the average between two Dates.

 

[Fluff]

Do you mean from 16 Jun to 1 Nov for each year?

 

[DRSteele]

I do not. I mean the portion of each of those years between Nov and Jun.

So in Eg3, Nov2017, Dec2017, Jan2018 etc to Jun2018, then Nov2018, Dec2018, Jan2019 etc to Jun2019, and onward until it's covered.

Think about analysing the weather over winter for each of the years of interest. Winter in my locale starts in Nov and doesn't go away until June.

 

[Fluff]

In that case I have no idea.

 

[KRice]

Try this...I didn't use FILTER, but if this is what you want, a FILTER version could be created.

mrexcel_20200408.xlsx
	B	C	D	E	F		H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2									Example1				Example2				Example3				Example4
3									Start	End			Start	End			Start	End			Start	End
4								Month	4	11			4	5			11	1			10	2
5	Date	Month	Day	Year	Value			Day	11	14			20	7			6	16			23	9
6	4/11/2010	4	11	2010	-10			Year	2010	2013			2011	2015			2017	2020			2019	2020
7	4/12/2010	4	12	2010	8			Date	4/11/2010	11/14/2013			4/20/2011	5/7/2015			11/6/2017	1/16/2020			10/23/2019	2/9/2020
8	4/13/2010	4	13	2010	-7
9	4/14/2010	4	14	2010	4				Start Date	End Date	Avg Value		Start Date	End Date	Avg Value		Start Date	End Date	Avg Value		Start Date	End Date	Avg Value
10	4/15/2010	4	15	2010	9				4/11/2010	11/14/2010	0.665		4/20/2011	5/7/2011	-0.111		11/6/2017	1/16/2018	-0.417		10/23/2019	2/9/2020	0.664
11	4/16/2010				10				4/11/2011	11/14/2011	-0.307		4/20/2012	5/7/2012	0.500		11/6/2018	1/16/2019	0.056
12	4/17/2010				6				4/11/2012	11/14/2012	0.216		4/20/2013	5/7/2013	1.889		11/6/2019	1/16/2020	0.444
13	4/18/2010				-2				4/11/2013	11/14/2013	0.284		4/20/2014	5/7/2014	1.889
14	4/19/2010				5								4/20/2015	5/7/2015	-1.000
15	4/20/2010				6
16	4/21/2010				5
17	4/22/2010				6
18	4/23/2010				6
19	4/24/2010				-10
20	4/25/2010				3
21	4/26/2010				1
22	4/27/2010				-3
23	4/28/2010				9
24	4/29/2010				-8
Sheet10

Cell Formulas
Range	Formula
C6:C10	C6	=MONTH(B6)
D6:D10	D6	=DAY(B6)
E6:E10	E6	=YEAR(B6)
V7:W7,R7:S7,N7:O7,J7:K7	J7	=DATE(J6,J4,J5)
J10,V10,R10,N10	J10	=J$7
K10,W10,S10,O10	K10	=DATE(MAX(IFERROR(DATEDIF(J$7,DATE(J$6,K$4,K$5),"d"),J$6+1),J$6),K$4,K$5)
X10:X19,T10:T19,P10:P19,L10:L19	L10	=IFERROR(AVERAGEIFS($F$6:$F$3659,$B$6:$B$3659,">="&J10,$B$6:$B$3659,"<="&K10),"")
V11:V19,R11:R19,N11:N19,J11:J19	J11	=IF(OR(K10=K$7,K10=""),"",DATE(YEAR(J10)+1,MONTH(J10),DAY(J10)))
W11:W19,S11:S19,O11:O19,K11:K19	K11	=IF(J11="","",DATE(YEAR(K10)+1,MONTH(K10),DAY(K10)))
F6:F24	F6	=RANDBETWEEN(-10,10)

 

[KRice]

Here is a slightly revised approach for the K10 formula. I didn't like comparing the number of days between two dates to a year...it works okay because of how the check is done to determine whether the month and day specified for the end date span into the next year, but this approach is a little cleaner. It sets a large dummy value if that condition is met, and then depending on whether the dummy value is generated, the year is set as either the same as the start year or the following year.

The revised version for cells K10,W10,S10,O10, pasted into K10 and then copied into the other cells mentioned:
=DATE(IF(IFERROR(DATEDIF(J$7,DATE(J$6,K$4,K$5),"d"),9E+99)=9E+99,J$6+1,J$6),K$4,K$5)