# Same formula 2 different results

#### gtd526

##### Active Member
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.

Cell Formulas
RangeFormula
B1,H1,E1B1=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,E2B2=COUNTIF(InjuryList!\$A:\$A,B1)
B3,H3,E3B3=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:C15C3=IFERROR(VLOOKUP(B3,Player_Totals_Table[[Player]:[FG%]],10,0),"")
B4,H4,E4B4=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,E5B5=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,E6B6=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,E7B7=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,E8B8=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,E9B9=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,E10B10=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,E11B11=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,E12B12=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,E13B13=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,E14B14=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,E15B15=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)),"")
A5A5=MAXIFS(D3:BX15,D3:BX15,"<>0",D3:BX15,"<>""")
A7A7=MAXIFS(D16:BX16,D16:BX16,"<>0",D16:BX16,"<>""")
A9,A17A9=OFFSET(INDEX(\$D\$16:\$BX\$16,MATCH(MAX(\$D\$16:\$BX\$16),\$D\$16:\$BX\$16,0)),-15,-2)
A11A11=IFERROR(INDEX(\$1:\$1,AGGREGATE(15,6,COLUMN(\$B\$1:\$BV\$1)/(\$D\$3:\$BX\$15=\$A\$5)/(\$D\$2:\$BX\$2="Pts"),1)),"")
A14A14=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:D15D3=IFERROR(VLOOKUP(B3,Player_Totals_Table[[Player]:[PTS]],29,0),"")
D16,J16,G16D16=IFERROR(AVERAGEIFS(D3:D15,D3:D15,"<>0"),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Fluff

##### MrExcel MVP, Moderator
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
Excel Formula:
``=INDEX(B1:BV1,MATCH(MAX(D16:BX16),D16:BX16,0))``

#### gtd526

##### Active Member
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
Excel Formula:
``=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

##### MrExcel MVP, Moderator
Not without the entire workbook. Have you enabled iterative calculation?

#### gtd526

##### Active Member

Not without the entire workbook. Have you enabled iterative calculation?
Yes. But MAX Iterations:1 MAX Change: .001

#### Fluff

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

#### gtd526

##### Active Member
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
ABCD
16UTAAvg:112
17NO
18
19Offset MAX Avg Pts:
20NO
21correct result, not A9??
Injuries
Cell Formulas
RangeFormula
D16D16=IFERROR(AVERAGEIFS(D3:D15,D3:D15,"<>0"),"")
A20,A16:A17A16=OFFSET(INDEX(\$D\$16:\$BX\$16,MATCH(MAX(\$D\$16:\$BX\$16),\$D\$16:\$BX\$16,0)),-15,-2)

#### Fluff

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

Replies
3
Views
104
Replies
6
Views
153
Replies
13
Views
153
Replies
0
Views
61
Replies
4
Views
100

1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back