+ Reply to Thread
Results 1 to 2 of 2

Sorting teams accurately as per win/losses differential and points

  1. #1
    Registered User
    Join Date
    10-21-2011
    Location
    South Carolina
    MS-Off Ver
    Excel 2010
    Posts
    22

    Sorting teams accurately as per win/losses differential and points

    Hi,

    I have an issue where I am entering data in hidden columns (data taken from another worksheet but not present in this example as it is not required) and where the standings are not in the correct order (column T) in relations to the Win/Losses column. Team 4 should be in last place because they don't have any points. Should I have some kind of sorting from the largest to the smallest taking into consideration the point differential for the teams having the same amount of points to determine their respective positions? If so, how to I go about doing that. Maybe my formulas are wrong in columns V to AT. Any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Sorting teams accurately as per win/losses differential and points

    There's a lot going on here, so I'm hoping I've hit upon the fix you need. I used the following formula in D5; it should be array-entered (confirmed with Ctrl + Shift + Enter instead of Enter):

    =INDEX($AE$5:$AE$10,SUMPRODUCT(--($AT$5:$AT$10+0.0001*$AS$5:$AS$10+0.0000001*ROW($AS$5:$AS$10)=LARGE($AT$5:$AT$10+0.0001*$AS$5:$AS$10+0.0000001*ROW($AS$5:$AS$10),ROW(1:1))),ROW($AS$5:$AS$10)-ROW($AS$5)+1))

    Fill it down through D10 and it should return the teams in order by points then differential, based solely on the data in AE, AS, and AT. It looks like the rest of your table's lookups then carry over the correct data based on the team name in D, but I didn't parse through it all. Take a look at the attachment to see if it's working as desired:
    Attached Files Attached Files
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

+ 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. Replies: 6
    Last Post: 11-02-2018, 02:19 AM
  2. Updating teams points based on match outcome.
    By trueslawted in forum Excel General
    Replies: 1
    Last Post: 05-09-2018, 11:13 AM
  3. Replies: 6
    Last Post: 10-04-2014, 03:53 PM
  4. Ranking teams in order based on points
    By Losguapos1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-17-2014, 11:41 AM
  5. Calculating Wins/Losses for teams
    By mracine923 in forum Excel General
    Replies: 3
    Last Post: 06-08-2011, 01:52 PM
  6. [SOLVED] how to set up Bowls league 12 teams points and shot difference
    By Mel in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM
  7. [SOLVED] how to set up Bowls league 12 teams points and shot difference
    By Mel in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM

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