+ Reply to Thread
Results 1 to 4 of 4

Excluding ranges from arrays

  1. #1
    Registered User
    Join Date
    02-10-2011
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    92

    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
    Last edited by Johnmus; 03-05-2011 at 02:42 PM.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,849

    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?
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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......
    Last edited by daddylonglegs; 03-04-2011 at 09:53 PM.
    Audere est facere

  4. #4
    Registered User
    Join Date
    02-10-2011
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Excluding ranges from arrays

    Thanks daddylonglegs and MarvinP. Both your suggestions work!

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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