Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Excel Question

Options
  • 24-11-2014 9:27pm
    #1
    Clauric
    Registered Users Posts: 610 ✭✭✭


    I have a question regarding MS Excel, and I'm hoping somebody might be able to help. (Mods, please move if this is in the wrong place.)

    Long story:

    I have a list of of clubs (100+), and their results against each other over a number of years. Each match has a possible 5 results:
    • Win by X points
    • Win by less than X points
    • Draw
    • Lose by less than X points
    • Lose by more than X points

    Each of these results have a variable number of ranking points available, depending on the ranking of both the clubs.

    Each club starts on the same number of ranking points, and the points range from 0 - 100.

    After the first set of results, some of the clubs will have gone up, and some will have gone down. My issue is how to get Excel to find the latest ranking points for the club.

    I have the starting ranking points for each club in a table. However, if I add the rankings to the initial table, then it creates a circular reference. I need Excel to draw the search the lastest "New Rankings" and enter that rankings into the starting rankings for the next set of results.

    Home Old Ranking H Away Old Ranking A H Score A Score Home New Ranking H Away New Ranking A
    Club A 9 Club B 9 0 2 Club A 10 Club B 8
    Club C 9 Club D 9 2 2 Club C 9 Club D 9
    Club D From the new rankings Club A From the new rankings 1 1 Club D Club A
    Club B From the new rankings Club C From the new rankings 3 2 Club B Club C


    Does Excel have a formula or function to be able to search a 2 sets of columns, and return the latest ranking from either column set, whichever is the latest result?

    I hope that makes sense.

    Short story:
    Is there any way I can get Excel to search for the last reference of a particular name across two column sets, and to select the a reference based on it's location?


Comments

  • Options
    #2
    KAGY
    Registered Users Posts: 1,091 ✭✭✭KAGY


    Doesn't really make sense but I'm on my phone so the table didn't look right
    You may be looking at something involving index and match.

    Index (table, row number, column number)
    Where you could use match (item, list) to return the row number or match(max (dates) , list of dates) to pick out the latest date


Advertisement