Same formula 2 different results

gtd526

Active Member
Joined
Jul 30, 2013
Messages
329
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 30, 2013
Messages
329
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
Not without the entire workbook. Have you enabled iterative calculation?
 

gtd526

Active Member
Joined
Jul 30, 2013
Messages
329
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
In that case there's not much I can do to help any further as I keep clear of circular references.
 

gtd526

Active Member
Joined
Jul 30, 2013
Messages
329
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
Both that formula & the one I suggested work fine for me, regardless of where they are.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top