Hi.
I am creating a competiton spreadsheet that will rank groups overall (they are listed by division) and breaks any ties with the value in the C column. There are 12 groups in each division and an exhibition group. They are placed in an overall ranking format. The top group receives a special award (G82) and the next ten after that are ranked 1st through 10th. (listed at the bottom of the sheet)
The score from the exhibition group should not count in the overall ranking so I have excluded # 16,29,42,55, and 68 from the ranges. See formula:
=IF(G4="","",RANK(G4,($G$4:$G$15,$G$17:$G$28,$G$30:$G$41,$G$43:$G$54,$G$56:$G$67,$G$69:$G$80),0)+SUMPRODUCT(--($G$4:$G$80=G4),--($C$4:$C$80>C4))-COUNTIFS($G$4:$G$80,LEFT($G$82,FIND(" ",$G$82))+0,$B$4:$B$80,MID($G$82,FIND(" ",$G$82)+1,255)))
The problem occurs when an exhibition groups ties another group for 1st, then the SUMPRODUCT arrays get screwed up and it starts ranking from 2 and causing a #N/A in the 1st place listing.
Any ideas how to exclude the exhibition groups from the array? I am attaching a sample sheet.
Thanks
Bookmarks