Same formula 2 different results

[gtd526]

Hello,
I have the same formula in different cells, but 2 different results.
I want A9 to be the correct result. A17 has the correct result, why not A9?
Im only showing part of the spreadsheet, because the range is large, but the formulas are shown.
thank you.

NBA.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Team:	NO			UTA			OKC
2	Injuries:	1	FG%	Pts	3	FG%	Pts	3	FG%	Pts
3	Who:	Lonzo Ball	39%	112	Derrick Favors	58%	74	Trevor Ariza
4	Max Pts:				Joe Ingles	47%	89	Al Horford	42%	100
5	112				Juwan Morgan	0%	0	Ty Jerome
6	Max Avg Pts:
7	112
8	Offset MAX Avg Pts:
9	UTA
10	Team Max Pts:
11	NO
12
13
14
15
16	Offset MAX Avg Pts:		Avg:	112		Avg:	81.5		Avg:	100
17	NO
18	correct result, not A9??
Injuries

Cell Formulas
Range	Formula
B1,H1,E1	B1	=LOOKUP(2, 1/((COUNTIF($B$1:B1,Favs!$A$4:$A$29)=0)*(Favs!$A$4:$A$29<>"")),Favs!$A$4:$A$29)
B2,H2,E2	B2	=COUNTIF(InjuryList!$A:$A,B1)
B3,H3,E3	B3	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),1)),"")
I3:I15,F3:F15,C3:C15	C3	=IFERROR(VLOOKUP(B3,Player_Totals_Table[[Player]:[FG%]],10,0),"")
B4,H4,E4	B4	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),2)),"")
B5,H5,E5	B5	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),3)),"")
B6,H6,E6	B6	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),4)),"")
B7,H7,E7	B7	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),5)),"")
B8,H8,E8	B8	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),6)),"")
B9,H9,E9	B9	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),7)),"")
B10,H10,E10	B10	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),8)),"")
B11,H11,E11	B11	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),9)),"")
B12,H12,E12	B12	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),10)),"")
B13,H13,E13	B13	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),11)),"")
B14,H14,E14	B14	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),12)),"")
B15,H15,E15	B15	=IFERROR(INDEX(InjuryList!$B$3:$B$202,SMALL(IF(B$1=InjuryList!$A$3:$A$200,ROW(InjuryList!$A$3:$A$200)-ROW(InjuryList!$A$3)+1),13)),"")
A5	A5	=MAXIFS(D3:BX15,D3:BX15,"<>0",D3:BX15,"<>""")
A7	A7	=MAXIFS(D16:BX16,D16:BX16,"<>0",D16:BX16,"<>""")
A9,A17	A9	=OFFSET(INDEX($D$16:$BX$16,MATCH(MAX($D$16:$BX$16),$D$16:$BX$16,0)),-15,-2)
A11	A11	=IFERROR(INDEX($1:$1,AGGREGATE(15,6,COLUMN($B$1:$BV$1)/($D$3:$BX$15=$A$5)/($D$2:$BX$2="Pts"),1)),"")
A14	A14	=IFERROR(INDEX($1:$1,AGGREGATE(15,6,COLUMN($B$1:$BV$1)/($D$16:$BX$16=$A$7)/($D$2:$BX$2="Pts"),14)),"")
J3:J15,G3:G15,D3:D15	D3	=IFERROR(VLOOKUP(B3,Player_Totals_Table[[Player]:[PTS]],29,0),"")
D16,J16,G16	D16	=IFERROR(AVERAGEIFS(D3:D15,D3:D15,"<>0"),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Without being able to evaluate the formula I can't say for certain, but may have something to do with the circular references you've got.
However you can get rid of the volatile offset function & use

=INDEX(B1:BV1,MATCH(MAX(D16:BX16),D16:BX16,0))

 

[gtd526]

Without being able to evaluate the formula I can't say for certain, but may have something to do with the circular references you've got.
However you can get rid of the volatile offset function & use

=INDEX(B1:BV1,MATCH(MAX(D16:BX16),D16:BX16,0))

I get the same result with your new formula.
Is there anything I can give you to 'evaluate the formula'?

 

[Fluff]

Not without the entire workbook. Have you enabled iterative calculation?

 

[gtd526]

Not without the entire workbook. Have you enabled iterative calculation?

Yes. But MAX Iterations:1 MAX Change: .001

 

[Fluff]

In that case there's not much I can do to help any further as I keep clear of circular references.

 

[gtd526]

In that case there's not much I can do to help any further as I keep clear of circular references.

No problem. I noticed, if the formula is below the Avg Row (16:16), it works fine. But not A16.

NBA.xlsm
	A	B	C	D
16	UTA		Avg:	112
17	NO
18
19	Offset MAX Avg Pts:
20	NO
21	correct result, not A9??
Injuries

Cell Formulas
Range	Formula
D16	D16	=IFERROR(AVERAGEIFS(D3:D15,D3:D15,"<>0"),"")
A20,A16:A17	A16	=OFFSET(INDEX($D$16:$BX$16,MATCH(MAX($D$16:$BX$16),$D$16:$BX$16,0)),-15,-2)

 

[Fluff]

Both that formula & the one I suggested work fine for me, regardless of where they are.