+ Reply to Thread
Results 1 to 2 of 2

Build dynamic Ranking in Excel

  1. #1
    Registered User
    Join Date
    10-25-2006
    Posts
    48

    Build dynamic Ranking in Excel

    Hello, I would like to build a dynamic ranking using excel 2010. So as soon as i populate the results, the ranking get updated automatically.
    It has 20 teams facing each other. Could someone advise on this, please ?

    Please Login or Register  to view this content.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Build dynamic Ranking in Excel

    I couldn't figure out a way to float the non-contiguous ranges, so I did it with a helper table.

    Feel free to put the helper table in another location, such as a tab you hide afterwards. I had mine in M2:P5.

    The first cell in the helper table:

    =IF(ISERROR(INDEX($B$2:$B$5, MATCH(0, COUNTIF(M1,$B$2:$B$5), 0))), INDEX($F$2:$F$5, MATCH(0, COUNTIF(M1, $F$2:$F$5), 0)), INDEX($B$2:$B$5, MATCH(0, COUNTIF($M$1:M1, $B$2:$B$5), 0)))

    This is an array formula, confirmed with Ctrl+Shift+Enter.

    Second column, Score summary:

    =SUMIF($B$2:$B$5,M2,$C$2:$C$5)+SUMIF($F$2:$F$5,M2,$E$2:$E$5)

    Third column, Wins

    =SUMPRODUCT(($B$2:$B$5=$M2)*($C$2:$C$5>$E$2:$E$5))+SUMPRODUCT(($F$2:$F$5=$M2)*($E$2:$E$5>$C$2:$C$5))

    Fourth column, Ties and Losses

    =SUMPRODUCT(($B$2:$B$5=$M2)*($C$2:$C$5<=$E$2:$E$5))+SUMPRODUCT(($F$2:$F$5=$M2)*($E$2:$E$5<=$C$2:$C$5))))

    Drag all formulas down as far as needed; this will pull all unique team names and respective stats.



    Then the actual results is sorted and indexed with this array formula:
    =INDEX(M$2:M$5,SMALL(IF($N$2:$N$5=LARGE($N$2:$N$5,ROW(A1)),ROW($I$1:$I$4)),ROW($A$1)))

    This single formula is then copied down and over as needed.
    Attached Files Attached Files
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to build a dynamic matrix table with user defined no.of rows and no.of columns
    By santhosh51 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 08-20-2013, 03:58 AM
  2. [SOLVED] How Can I Build A Dynamic List From Multiple Named Ranges
    By GEANZ in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-02-2013, 01:56 AM
  3. [SOLVED] Dynamic Ranking based on Conditions
    By compd1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-07-2013, 07:30 AM
  4. Build a Dynamic Named Range from criteria.
    By sinspawn56 in forum Excel General
    Replies: 8
    Last Post: 01-15-2013, 04:40 AM
  5. Dynamic Ranking of Yearly Totals
    By MSchibs in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-07-2010, 11:30 PM

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