Median which Excludes Zeros Using the Index/Match Function across Worksheets

[94mustang]

Hello Excel Forum Team,

I am using the desktop client version of Excel (Microsoft 365 MSO Version 2303). I am needing to calculate the Median across multiple worksheets which exclude zeros or text. I realized that the Median is NOT removing the zeros or text from the other worksheets. As you can see in my post, I am using the Index/Match function to calculate the Median. The other thing I noticed was that the median will not calculate unless all worksheets are filled in. How can the Median function be written so that I can calculate the matching identifier to the other worksheets while excluding the zeros or text in the cells. My company has disabled macros so I cannot seem to figure out how to post a "mini-sheet" using XL2BB. I do have it as an add-in, but it will not let me select a range. See screenshot below. I will do my best to provide the formula in the Score column. Formula is below the screenshot.

A further note, all the risks and opportunities will be copied to all the worksheets, and the unique Identifier will stay with the same row (record) of information. Each row will have a unique identifier number associated with based on the selection of the Value Proposition and Risk/Opportunity columns.

The formulas below are in a summary sheet which is the screenshot above.

B:B Col
=IF(OR(F3="",G3=""),"",CONCATENATE(LEFT(F3,1),LEFT(G3,1)))

C:C Col
=IF(B3="","",CONCATENATE(B3," - ",TEXT(COUNTIF($B$3:B3,B3),"000")))

H:H Col
=IFERROR(MEDIAN(
INDEX(Assoc01!$L$3:$L$201,MATCH(C3,Assoc01!$C$3:$C$201,0)),
INDEX(Assoc02!$L$3:$L$201,MATCH(C3,Assoc02!$C$3:$C$201,0)),
INDEX(Assoc03!$L$3:$L$201,MATCH(C3,Assoc03!$C$3:$C$201,0)),
INDEX(Assoc04!$L$3:$L$201,MATCH(C3,Assoc04!$C$3:$C$201,0)),
INDEX(Assoc05!$L$3:$L$201,MATCH(C3,Assoc05!$C$3:$C$201,0)),
INDEX(Assoc06!$L$3:$L$201,MATCH(C3,Assoc06!$C$3:$C$201,0)),
INDEX(Assoc07!$L$3:$L$201,MATCH(C3,Assoc07!$C$3:$C$201,0)),
INDEX(Assoc08!$L$3:$L$201,MATCH(C3,Assoc08!$C$3:$C$201,0)),
INDEX(Assoc09!$L$3:$L$201,MATCH(C3,Assoc09!$C$3:$C$201,0)),
INDEX(Assoc10!$L$3:$L$201,MATCH(C3,Assoc10!$C$3:$C$201,0))),"")

Basically, if each sheet (Assoc01, Assoc02, etc) has a value (col L: Score), then I want the value to be included in the median, otherwise, exclude empty cells, zeros, or text. Below is a visual of the "Assoc01" worksheet.

 

[Fluff]

How about

=LET(v,CHOOSECOLS(VSTACK(Assoc1:Assoc10!C$3:L$201),1,-1),MEDIAN(INDEX(FILTER(v,(INDEX(v,,1)=C3)*(ISNUMBER(INDEX(v,,2)))),,2)))

Re XL2BB have a look here XL2BB Icons greyed out

 

[94mustang]

Thank you for the XL2BB link. That link resolved my issue so thank you so much.

I am getting the "#NAME?" error in the cell. See below.

Risks and Opps Log.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2		Abbr	Identifier	Date Added	Description	Value Proposition	Risk/Opportunity	Score	Range	Detail Current Controls
3		CR	CR - 001			Coach/Mentor	Risk	#NAME?	19
4
Risks and Opps Log Summary

Cell Formulas
Range	Formula
B3:B4	B3	=IF(OR(F3="",G3=""),"",CONCATENATE(LEFT(F3,1),LEFT(G3,1)))
C3:C4	C3	=IF(B3="","",CONCATENATE(B3," - ",TEXT(COUNTIF($B$3:B3,B3),"000")))
H3	H3	=LET(v,CHOOSECOLS(VSTACK(Assoc1:Assoc10!C$3:L$201),1,-1),MEDIAN(INDEX(FILTER(v,(INDEX(v,,1)=C3)*(ISNUMBER(INDEX(v,,2)))),,2)))
I3:I4	I3	=IFERROR(IF(MAX( INDEX(Assoc01!$L$3:$L$301,MATCH(C3,Assoc01!$C$3:$C$301,0)), INDEX(Assoc02!$L$3:$L$301,MATCH(C3,Assoc02!$C$3:$C$301,0)), INDEX(Assoc03!$L$3:$L$301,MATCH(C3,Assoc03!$C$3:$C$301,0)), INDEX(Assoc04!$L$3:$L$301,MATCH(C3,Assoc04!$C$3:$C$301,0)), INDEX(Assoc05!$L$3:$L$301,MATCH(C3,Assoc05!$C$3:$C$301,0)), INDEX(Assoc06!$L$3:$L$301,MATCH(C3,Assoc06!$C$3:$C$301,0)), INDEX(Assoc07!$L$3:$L$301,MATCH(C3,Assoc07!$C$3:$C$301,0)), INDEX(Assoc08!$L$3:$L$301,MATCH(C3,Assoc08!$C$3:$C$301,0)), INDEX(Assoc09!$L$3:$L$301,MATCH(C3,Assoc09!$C$3:$C$301,0)), INDEX(Assoc09!$L$3:$L$301,MATCH(C3,Assoc09!$C$3:$C$301,0)), INDEX(Assoc10!$L$3:$L$301,MATCH(C3,Assoc10!$C$3:$C$301,0))) -MIN( INDEX(Assoc01!$L$3:$L$301,MATCH(C3,Assoc01!$C$3:$C$301,0)), INDEX(Assoc02!$L$3:$L$301,MATCH(C3,Assoc02!$C$3:$C$301,0)), INDEX(Assoc03!$L$3:$L$301,MATCH(C3,Assoc03!$C$3:$C$301,0)), INDEX(Assoc04!$L$3:$L$301,MATCH(C3,Assoc04!$C$3:$C$301,0)), INDEX(Assoc05!$L$3:$L$301,MATCH(C3,Assoc05!$C$3:$C$301,0)), INDEX(Assoc06!$L$3:$L$301,MATCH(C3,Assoc06!$C$3:$C$301,0)), INDEX(Assoc07!$L$3:$L$301,MATCH(C3,Assoc07!$C$3:$C$301,0)), INDEX(Assoc08!$L$3:$L$301,MATCH(C3,Assoc08!$C$3:$C$301,0)), INDEX(Assoc09!$L$3:$L$301,MATCH(C3,Assoc09!$C$3:$C$301,0)), INDEX(Assoc09!$L$3:$L$301,MATCH(C3,Assoc09!$C$3:$C$301,0)), INDEX(Assoc10!$L$3:$L$301,MATCH(C3,Assoc10!$C$3:$C$301,0)))=0,"", MAX( INDEX(Assoc01!$L$3:$L$301,MATCH(C3,Assoc01!$C$3:$C$301,0)), INDEX(Assoc02!$L$3:$L$301,MATCH(C3,Assoc02!$C$3:$C$301,0)), INDEX(Assoc03!$L$3:$L$301,MATCH(C3,Assoc03!$C$3:$C$301,0)), INDEX(Assoc04!$L$3:$L$301,MATCH(C3,Assoc04!$C$3:$C$301,0)), INDEX(Assoc05!$L$3:$L$301,MATCH(C3,Assoc05!$C$3:$C$301,0)), INDEX(Assoc06!$L$3:$L$301,MATCH(C3,Assoc06!$C$3:$C$301,0)), INDEX(Assoc07!$L$3:$L$301,MATCH(C3,Assoc07!$C$3:$C$301,0)), INDEX(Assoc08!$L$3:$L$301,MATCH(C3,Assoc08!$C$3:$C$301,0)), INDEX(Assoc09!$L$3:$L$301,MATCH(C3,Assoc09!$C$3:$C$301,0)), INDEX(Assoc09!$L$3:$L$301,MATCH(C3,Assoc09!$C$3:$C$301,0)), INDEX(Assoc10!$L$3:$L$301,MATCH(C3,Assoc10!$C$3:$C$301,0))) -MIN( INDEX(Assoc01!$L$3:$L$301,MATCH(C3,Assoc01!$C$3:$C$301,0)), INDEX(Assoc02!$L$3:$L$301,MATCH(C3,Assoc02!$C$3:$C$301,0)), INDEX(Assoc03!$L$3:$L$301,MATCH(C3,Assoc03!$C$3:$C$301,0)), INDEX(Assoc04!$L$3:$L$301,MATCH(C3,Assoc04!$C$3:$C$301,0)), INDEX(Assoc05!$L$3:$L$301,MATCH(C3,Assoc05!$C$3:$C$301,0)), INDEX(Assoc06!$L$3:$L$301,MATCH(C3,Assoc06!$C$3:$C$301,0)), INDEX(Assoc07!$L$3:$L$301,MATCH(C3,Assoc07!$C$3:$C$301,0)), INDEX(Assoc08!$L$3:$L$301,MATCH(C3,Assoc08!$C$3:$C$301,0)), INDEX(Assoc09!$L$3:$L$301,MATCH(C3,Assoc09!$C$3:$C$301,0)), INDEX(Assoc09!$L$3:$L$301,MATCH(C3,Assoc09!$C$3:$C$301,0)), INDEX(Assoc10!$L$3:$L$301,MATCH(C3,Assoc10!$C$3:$C$301,0)))),"")
H4	H4	=IFERROR(MEDIAN( INDEX(Assoc01!$L$3:$L$201,MATCH(C4,Assoc01!$C$3:$C$201,0)), INDEX(Assoc02!$L$3:$L$201,MATCH(C4,Assoc02!$C$3:$C$201,0)), INDEX(Assoc03!$L$3:$L$201,MATCH(C4,Assoc03!$C$3:$C$201,0)), INDEX(Assoc04!$L$3:$L$201,MATCH(C4,Assoc04!$C$3:$C$201,0)), INDEX(Assoc05!$L$3:$L$201,MATCH(C4,Assoc05!$C$3:$C$201,0)), INDEX(Assoc06!$L$3:$L$201,MATCH(C4,Assoc06!$C$3:$C$201,0)), INDEX(Assoc07!$L$3:$L$201,MATCH(C4,Assoc07!$C$3:$C$201,0)), INDEX(Assoc08!$L$3:$L$201,MATCH(C4,Assoc08!$C$3:$C$201,0)), INDEX(Assoc09!$L$3:$L$201,MATCH(C4,Assoc09!$C$3:$C$201,0)), INDEX(Assoc10!$L$3:$L$201,MATCH(C4,Assoc10!$C$3:$C$201,0))),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:R201	Expression	=$N3="Extreme"	text	NO
B3:R201	Expression	=$N3="High"	text	NO
B3:R201	Expression	=$N3="Medium"	text	NO
B3:R201	Expression	=$N3="Low"	text	NO
B3:R201	Expression	=$N3="Very Low"	text	NO
B3:R201	Expression	=MOD(ROW(),2)=0	text	NO
B3:R201	Expression	=MOD(ROW(),2)=1	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E3:F201	List	Educate,Coach/Mentor,Partner,Recognize,Discover
G3:G201	List	='R&O Criteria'!$B$38:$C$38

 

[Fluff]

You shouldn't be getting a #name error with version 2303 of 365.
Do you have the VSTACK function?

 

[94mustang]

Yes, I do have the VSTACK function. I, too, was looking to see if I had that function to see if I could figure out why I was getting this error. I also checked the LET, CHOOSECOLS, & FILTER because I have never used those in a function before and I do have all of them in my version of Excel.

 

[94mustang]

Does this help?

 

[Fluff]

I missed the leading 0 on the sheet number for the 1st sheet, try

=LET(v,CHOOSECOLS(VSTACK(Assoc01:Assoc10!C$3:L$201),1,-1),MEDIAN(INDEX(FILTER(v,(INDEX(v,,1)=C3)*(ISNUMBER(INDEX(v,,2)))),,2)))

 

[94mustang]

Works perfectly. You were a tremendous help and have introduced me to new functions to study and understand. Thank you so very much.

 

[Fluff]

Glad to help & thanks for the feedback.