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. |