Average specific value if listed on any worksheet

[gtd526]

Hello,
Im able to average a certain range (L:L) for ALL spreadsheets in workbook ie AVERAGE('Schwab:401k'!$L$9:$L$15)
Want to average only a single symbol (TECL) if its present on any spreadsheet. It could be listed multiple times on a single sheet.
I've tried a couple of different formulas, but no luck (C33 & C34).
Thank you.

Sample:

The Whole Enchilada.xlsm
	A	B	C
32		14.50%	Average for ALL sheets for L:L Range
33		TECL	#REF!
34	Schwab	52.11%	#VALUE!
35	Roth IRA	18.08%
36	AMTD	-0.98%
37	401k	1.64%
Capital Gains Tax

Cell Formulas
Range	Formula
B32	B32	=AVERAGE('Schwab:401k'!$L$9:$L$15)
C33	C33	=IF('Schwab:401k'!$A$9:$A$15="TECL",AVERAGE('Schwab:401k'!$L$9:$L$15),"Wrong")
C34	C34	=IF(INDIRECT("'"&A34&"'"&"!"&"A9:A15")="TECL",AVERAGE(INDIRECT("'"&A34&"'"&"!"&"L9:L15")),"Wrong")
B34	B34	=AVERAGE(INDIRECT("'"&A34&"'"&"!"&"L9:L15"))
B35:B37	B35	=AVERAGE(INDIRECT("'"&A35&"'"&"!"&"A9:A15"),INDIRECT("'"&A35&"'"&"!"&"L9:L15"))
Press CTRL+SHIFT+ENTER to enter array formulas.

First Spreadsheet:

The Whole Enchilada.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
8	Symbol	Sector	Type	Shares	Price	CURRENT	MAX Value	MIN Value	Cost Value	Market Value	Gain/Loss	G/L %
9	TECL	Tech 3X	Long	86	$27.83	$54.02	$54.02	$49.49	$2,393.38	$ 4,645.72	$ 2,252.34	94.11%
10	FAS	Finance 3x	Long	13	$57.74	$63.58	$63.58	$58.51	$750.62	$ 826.54	$ 75.92	10.11%
11
12
13
14
15
Schwab

Cell Formulas
Range	Formula
F9:F15	F9	=IF(ISTEXT($A9),VLOOKUP($A9,'All Tickers'!$H$11:$I$110,2,0),"")
G9:G15	G9	=IF(ISBLANK($A9),"",IF($F9=0,0,MAX($F9,$G9)))
H9:H15	H9	=IF(ISBLANK($A9),"",IF($H9=0,$F9,MEDIAN($H9,$F9,0)))
I9:I15	I9	=IF($A9="","",D9*E9)
J9:J15	J9	=IF($A9="","",$D9*$F9)
K9:K15	K9	=IF($A9="","",J9-I9)
L9:L10	L9	=IF($A9="","",($J9-$I9)/$I9)
L11:L15	L11	=IF($A11="","",K11/I11)
B9:B15	B9	=IF(COUNTIF(Sectors!$A$2:$AAJ$47,$A9)=1,INDEX(Sectors!$A$2:$AAJ$2,MAX((Sectors!$A$2:$AAJ$47=$A9)*(COLUMN(Sectors!$A$2:$AAJ$2)))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Second Spreadsheet:

The Whole Enchilada.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
8	Symbol	Sector	Type	Shares	Price	CURRENT	MAX Value	MIN Value	Cost	Market Value	Gain/Loss	G/L %
9	TECL	Tech 3X	Long	44	$45.70	$54.02	$54.02	$49.49	$2,010.80	$2,376.88	$366.08	18.21%
10	TECL	Tech 3X	Long	21	$45.80	$54.02	$54.02	$49.49	$961.80	$1,134.42	$172.62	17.95%
11
12
13
14
15
Roth IRA

Cell Formulas
Range	Formula
F9:F15	F9	=IF(ISTEXT($A9),VLOOKUP($A9,'All Tickers'!$H$11:$I$110,2,0),"")
G9:G15	G9	=IF(ISBLANK($A9),"",IF($F9=0,0,MAX($F9,$G9)))
H9:H15	H9	=IF(ISBLANK($A9),"",IF($H9=0,$F9,MEDIAN($H9,$F9,0)))
I9:I15	I9	=IF($A9="","",E9*D9)
J9:J15	J9	=IF($A9="","",$D9*$F9)
K9:K15	K9	=IF($A9="","",J9-I9)
L9:L15	L9	=IF($A9="","",K9/I9)
B9:B15	B9	=IF(COUNTIF(Sectors!$A$2:$AAJ$47,$A9)=1,INDEX(Sectors!$A$2:$AAJ$2,MAX((Sectors!$A$2:$AAJ$47=$A9)*(COLUMN(Sectors!$A$2:$AAJ$2)))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Third Spreadsheet:

The Whole Enchilada.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
8	Symbol	Sector	Type	Shares	Cost	CURRENT	MAX Value	MIN Value	Cost Value	Market Value	Gain/Loss	G/L %
9	CG	Finance	Long	25	$34.30	$31.95	$31.95	$30.00	$857.50	$798.75	-$58.75	-6.85%
10	TECL	Tech 3X	Long	14	$51.50	$54.02	$54.02	$24.75	$721.00	$756.28	$35.28	4.89%
11
12
13
14
15
AMTD

Cell Formulas
Range	Formula
F9:F15	F9	=IF(ISTEXT($A9),VLOOKUP($A9,'All Tickers'!$H$11:$I$110,2,0),"")
G9:G15	G9	=IF(ISBLANK($A9),"",IF($F9=0,0,MAX($F9,$G9)))
H9:H15	H9	=IF(ISBLANK($A9),"",IF($H9=0,$F9,MEDIAN($H9,$F9,0)))
I9:I15	I9	=IF($A9="","",D9*E9)
J9:J15	J9	=IF($A9="","",$D9*$F9)
K9:K15	K9	=IF($A9="","",J9-I9)
L9:L15	L9	=IF($A9="","",K9/I9)
B9:B15	B9	=IF(COUNTIF(Sectors!$A$2:$AAJ$47,$A9)=1,INDEX(Sectors!$A$2:$AAJ$2,MAX((Sectors!$A$2:$AAJ$47=$A9)*(COLUMN(Sectors!$A$2:$AAJ$2)))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Tetra201]

Maybe

=AVERAGE(AVERAGEIFS(INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!L9:L15"),INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!A9:A15"),"TECL"))

 

[gtd526]

Maybe

=AVERAGE(AVERAGEIFS(INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!L9:L15"),INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!A9:A15"),"TECL"))

Perfect!
Thank you!

 

[gtd526]

Maybe

=AVERAGE(AVERAGEIFS(INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!L9:L15"),INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!A9:A15"),"TECL"))

It worked for the first symbol, but not the rest.
"TECL" is the only symbol listed more than once. Can we obtain average whether its listed once or multiple times?

The Whole Enchilada.xlsm
	E	F
38	TECL	36.98%
39	TECL	36.98%
40	FAS	#DIV/0!
41	TSLL	#DIV/0!
42	TECL	36.98%
43	TECL	36.98%
44	CG	#DIV/0!
45	WBREOX	#DIV/0!
46	FBGKX	#DIV/0!
Capital Gains Tax

Cell Formulas
Range	Formula
F41:F46,F38:F39	F38	=AVERAGE(AVERAGEIFS(INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!$L$9:$L$15"),INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!$A$9:$A$15"),E38))
F40	F40	=AVERAGE(AVERAGEIFS(INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!$L$9:$L$15"),INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!$A$9:$A$15"),"FAS"))

 

[Tetra201]

Most likely, you are getting the #DIV/0! error not because of a symbol being listed just once, but because of the symbol being absent from or not having data on one or more sheets.

See if the following updated formula works for you:

=IFERROR(AVERAGE(IFERROR(AVERAGEIFS(INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!L9:L15"),INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!A9:A15"),E38),"")),"No Data")

Just a note: you don't need to use $L$9:$L$15 the INDIRECT function argument; just L9:L15 will work OK.

 

[gtd526]

Most likely, you are getting the #DIV/0! error not because of a symbol being listed just once, but because of the symbol being absent from or not having data on one or more sheets.

See if the following updated formula works for you:

=IFERROR(AVERAGE(IFERROR(AVERAGEIFS(INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!L9:L15"),INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!A9:A15"),E38),"")),"No Data")

Just a note: you don't need to use $L$9:$L$15 the INDIRECT function argument; just L9:L15 will work OK.

regarding: #DIV/0! error
there is data (L:L) regarding the lookup symbol, on a single spreadsheet (ie FAS, TSLL, CG).
i have the spreadsheets shown above.
some symbols are only listed once in on a single spreadsheet (ie FAS, TSLL, CG).

the second formula you replied with is only showing the G/L% from the first spreadsheet (Schwab).
I've add the IFERROR to the original formula (H:H).

The Whole Enchilada.xlsm
	E	F	G	H
34	Average (L:L) for symbols in ALL spreadsheets			Test
35	TECL	36.98%	91.23%	36.98%
36	FAS	#DIV/0!	16.40%	No Data
37	TSLL	#DIV/0!	0.44%	No Data
38	TECL	36.98%	91.23%	36.98%
39	TECL	36.98%	91.23%	36.98%
40	CG	#DIV/0!	No Data	No Data
Capital Gains Tax

Cell Formulas
Range	Formula
F35:F40	F35	=AVERAGE(AVERAGEIFS(INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!$L$9:$L$15"),INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!$A$9:$A$15"),E35))
G35:G40	G35	=IFERROR(AVERAGE(IFERROR(AVERAGEIFS(INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!L9:L15"),INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!A9:A15"),E35),"")),"No Data")
H35:H40	H35	=IFERROR(AVERAGE(AVERAGEIFS(INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!$L$9:$L$15"),INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!$A$9:$A$15"),E35)),"No Data")

 

[gtd526]

Most likely, you are getting the #DIV/0! error not because of a symbol being listed just once, but because of the symbol being absent from or not having data on one or more sheets.

See if the following updated formula works for you:

=IFERROR(AVERAGE(IFERROR(AVERAGEIFS(INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!L9:L15"),INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!A9:A15"),E38),"")),"No Data")

Just a note: you don't need to use $L$9:$L$15 the INDIRECT function argument; just L9:L15 will work OK.

I changed your formula to an array (G:G), now it works. Thank you.

The Whole Enchilada.xlsm
	E	F	G	H
35	TECL	46.97%	46.97%	46.97%
36	FAS	#DIV/0!	23.87%	No Data
37	FNGG	#DIV/0!	2.60%	No Data
38	TECL	46.97%	46.97%	46.97%
39	TECL	46.97%	46.97%	46.97%
40	CG	#DIV/0!	2.86%	No Data
41	WBREOX	#DIV/0!	5.71%	No Data
42	FBGKX	#DIV/0!	5.94%	No Data
Capital Gains Tax

Cell Formulas
Range	Formula
F35:F42	F35	=AVERAGE(AVERAGEIFS(INDIRECT("'"&"Schwab"&"'!$L$9:$L$15"),INDIRECT("'"&"Schwab"&"'!$A$9:$A$15"),E35),AVERAGEIFS(INDIRECT("'"&"Roth IRA"&"'!$L$9:$L$15"),INDIRECT("'"&"Roth IRA"&"'!$A$9:$A$15"),E35),AVERAGEIFS(INDIRECT("'"&"AMTD"&"'!$L$9:$L$15"),INDIRECT("'"&"AMTD"&"'!$A$9:$A$15"),E35))
G35:G42	G35	=IFERROR(AVERAGE(IFERROR(AVERAGEIFS(INDIRECT("'"&{"Schwab","Roth IRA","AMTD","401k"}&"'!L9:L15"),INDIRECT("'"&{"Schwab","Roth IRA","AMTD","401k"}&"'!A9:A15"),E35),"")),"No Data")
H35:H42	H35	=IFERROR(AVERAGE(AVERAGEIFS(INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!$L$9:$L$15"),INDIRECT("'"&{"Schwab","Roth IRA","AMTD"}&"'!$A$9:$A$15"),E35)),"No Data")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Tetra201]

Glad to have helped.