Help with Excel Use Case scenario (Function)

[vpandey4]

1. I have a set of data where we get work done by every employee on a weekly basis (eg. E01, E02) in man-days
2. I use that data to derive the velocity data set- (Work Done/5) and use it to derive the average velocity trend for coming weeks (using AVERAGE function)
   1. Employee list is selected via drop down and INDEX MATCH function used to find the respective work done for the week
3. If there is a particular week which I want to ignore from average velocity calculation for a specific employee (eg. E01), we capture it in a separate list
   1. based on ignore list- I want to capture NA() as a velocity for that week (when respective employee is selected) --> This is where I am having trouble to write the function for the same
   2. I use AVERAGEIF(range,"<>#N/A") to calculate the trend

Please suggest.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		SELECTION		CALCULATED VELOCITY					INPUT DATA				IGNORE LIST ENTRY
2										E01	E02		IGNORE LIST
3		E01		DATE	Velocity				WEEK	WORKDONE	WORKDONE		Emp	Date
4				08/06/2022	1				08/06/2022	5	5		E01	15/06/2022
5				15/06/2022	0.5				15/06/2022	2.5	5
6				22/06/2022	0.8				22/06/2022	4	8
7				29/06/2022	0.77				29/06/2022
8				06/07/2022	0.77				06/07/2022
9				13/07/2022	0.77				13/07/2022
10				20/07/2022	0.77				20/07/2022
11				27/07/2022	0.77				27/07/2022
12				03/08/2022	0.77				03/08/2022
13				10/08/2022	0.77				10/08/2022
14
Sheet1

Cell Formulas
Range	Formula
E4:E6	E4	=INDEX($I$2:$K$13,MATCH($D4,$I$2:$I$13,0),MATCH($B$3,$I$2:$K$2,0))/5
D5:D13,I5:I13	D5	=D4+7
E7:E13	E7	=AVERAGEIF($E$4:E6,"<>#N/A")

Named Ranges
Name	Refers To	Cells
sel	=Sheet1!$J$2:$K$2	E4:E6

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=sel

 

[vpandey4]

Got it working on my own using "=COUNTIFS($M$4:$M$8,$B$3,$N$4:$N$8,$D4)":

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		SELECTION		CALCULATED VELOCITY					INPUT DATA				IGNORE LIST ENTRY
2										E01	E02		IGNORE LIST
3		E01		DATE	Velocity	Formula			WEEK	WORKDONE	WORKDONE		Emp	Date
4				08/06/2022	1	1			08/06/2022	5	5		E01	01/06/2022
5				15/06/2022	0.5	#N/A			15/06/2022	2.5	5		E01	15/06/2022
6				22/06/2022	0.8	0.8			22/06/2022	4	8		E02	22/06/2022
7				29/06/2022	0.77	0.82			29/06/2022
8				06/07/2022	0.77	0.81			06/07/2022
9				13/07/2022	0.77	0.81			13/07/2022
10				20/07/2022	0.77	0.80			20/07/2022
11				27/07/2022	0.77	0.80			27/07/2022
12				03/08/2022	0.77	0.80			03/08/2022
13				10/08/2022	0.77	0.80			10/08/2022
14
Sheet1

Cell Formulas
Range	Formula
E4:E6	E4	=INDEX($I$2:$K$13,MATCH($D4,$I$2:$I$13,0),MATCH($B$3,$I$2:$K$2,0))/5
F4:F6	F4	=IF(COUNTIFS($M$4:$M$8,$B$3,$N$4:$N$8,$D4)=0,INDEX($I$2:$K$13,MATCH($D4,$I$2:$I$13,0),MATCH($B$3,$I$2:$K$2,0))/5,NA())
D5:D13,I5:I13	D5	=D4+7
E7:F13	E7	=AVERAGEIF($E$4:E6,"<>#N/A")

Named Ranges
Name	Refers To	Cells
sel	=Sheet1!$J$2:$K$2	E4:F6

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=sel