How to calculate the Average with Condition of Networking days from the reference dates

[shoun2502]

Hi Team,
I need a dynamic formula in Cell F2 which will stretched to N2 to calculate the
1. Average of Network days between "Request Received date and F1, G1, H1 and so on".
2. Consider those dates having Rows with Column A Status "Open"
3. Ignore Status "Open" , Request Received dates in Average where Any date in Request Received date >= Reference dates (as it will give the wrong average)
4. Answers that I need are there from Cell F3 to N3 ( can be used for crosscheck)

I tried using Formula below but getting #Value! Error . Dates in Cell F1 to N1 are in "Date format" Can anyone please help with the solution .

=AVERAGEIFS(NETWORKDAYS.INTL(Sheet1!$B$2:$B$5, F$1, 1), Sheet1!$A$2:$A$5, "Open", Sheet1!$B$2:$B$5, "<" & F$1)

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Status	Request Received				12/4/23	12/18/23	1/1/24	1/15/24	1/29/24	2/12/24	2/26/24	3/11/24	3/25/24
2	Closed	12/4/23			Drag formula ->	12	13.5	16	26	36	46	56	66	76
3	Open	12/31/23			Spill range ->	12	13.5	16	26	36	46	56	66	76
4	Open	11/17/23
5	Open	12/12/23
Sheet3

Cell Formulas
Range	Formula
F2:N2	G2	=AVERAGE(MAP(FILTER($B$2:$B$5,($A$2:$A$5="Open")*($B$2:$B$5<=G1)),LAMBDA(m,NETWORKDAYS(m,G1))))
F3:N3	F3	=BYCOL(F1:N1,LAMBDA(b,AVERAGE(MAP(FILTER($B$2:$B$5,($A$2:$A$5="Open")*($B$2:$B$5<=b)),LAMBDA(m,NETWORKDAYS(m,b))))))
Dynamic array formulas.

 

[shoun2502]

Thanks! Its working as per the expectations