# Excluding ranges from arrays

1. ## Excluding ranges from arrays

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

2. ## Re: Excluding ranges from arrays

Hi John,

In golf we do a scorecard playoff tiebreaker. That uses the scores on the last holes to determine the tiebreaker. I suggest you use the Excellence number and add it divided by 1000 to the Net scores to reduce possible ties.

Does that work for you?

3. ## Re: Excluding ranges from arrays

To solve your specific issue here you can add another "clause" to the SUMPRODUCT formula, i.e.

=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),--(\$J\$4:\$J\$80<>"Exh"))-COUNTIFS(\$G\$4:\$G\$80,LEFT(\$G\$82,FIND(" ",\$G\$82))+0,\$B\$4:\$B\$80,MID(\$G\$82,FIND(" ",\$G\$82)+1,255)))

..but I'd be inclined to re-write a little - you can simplify and streamline so that you can use this formula for the same results

=IF(G4="","",IF(J4="Exh","Exh",COUNTIFS(G\$4:G\$80,">"&G4,J\$4:J\$80,"<>Exh")+1+COUNTIFS(G\$4:G\$80,G4 ,C\$4:C\$80,">"&C4,J\$4:J\$80,"<>Exh")-SUMPRODUCT((G\$4:G\$80&" "&B\$4:B\$80=G\$82)+0)))

You can put that in I8 and copy all the way down, even through the Exh rows......

4. ## Re: Excluding ranges from arrays

I find the last formula re-write helps simplify the whole process.

I really appreciate your time!!!!!! Thanks!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1