+ Reply to Thread
Results 1 to 9 of 9

Dynamic Ranking Formula w/ Multiple Criteria

  1. 06-29-2011, 05:40 AM #1
    nwd9s
    nwd9s is offline
    Registered User
    Join Date
    02-15-2011
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    21

    Dynamic Ranking Formula w/ Multiple Criteria

    Hi all-

    I am looking for help with creating a ranking formula with multiple criteria. I have attached a sample file with what I am trying to do.

    On the report tab I have highlighted the cell in Yellow I want the formula. I am trying to match the country to the book value (two seperate tabs, but each cell would be the same place - i.e. the country on the country tab cell D40 would be the book value on the book value tab cell D40).

    Also, I need it to look up by date from the report tab, in the sample file it is September 2011.

    Any help with how to create this formula would be much appreciated.

    Thanks,
    nwd9s
    Attached Files Attached Files
    Last edited by nwd9s; 06-30-2011 at 07:35 AM.
    Reply With QuoteRegister To Reply
  2. 06-29-2011, 08:02 AM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Dynamic Ranking Formula w/ Multiple Criteria

    Can you give us the expected result for the current selections and how you arrive at it?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 06-29-2011, 09:11 AM #3
    nwd9s
    nwd9s is offline
    Registered User
    Join Date
    02-15-2011
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Dynamic Ranking Formula w/ Multiple Criteria

    THanks for your help NBVC-

    To answer your question, the expecint ranking for the USA would be 1. I was trying to use a formula like this, but could not get it to work:

    Please Login or Register  to view this content.
    Basically I need to match the countries in their tab to their corresponding book values in the book value tab.

    Hopefully this makes a bit more sense.

    Thanks,
    nwd9s
    Reply With QuoteRegister To Reply
  4. 06-29-2011, 09:13 AM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Dynamic Ranking Formula w/ Multiple Criteria

    Why is the answer in this case 1?
    Reply With QuoteRegister To Reply
  5. 06-29-2011, 09:18 AM #5
    nwd9s
    nwd9s is offline
    Registered User
    Join Date
    02-15-2011
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Dynamic Ranking Formula w/ Multiple Criteria

    Sorry, I see what you are saying, the value in the US should be 4,367.22. So the total rankings for september would look like this:

    I have attached a new sample file to show this.

    USA 4367.22284 1
    Brazil 4015.359406 2
    Russia 3094.299918 3
    Ethiopia 1577.496073 4
    South Korea 1425.021807 5
    Iceland 1320.325501 6
    China 1178.885269 7
    Canada 1062.191269 8
    Argentina 1031.78017 9
    Malaysia 968.3226827 10
    920.4741345 11
    New Zealand 891.958489 12
    Israel 875.7067579 13
    Fiji 841.3337839 14
    United Kingdom 827.3146918 15
    Bangladesh 752.5553239 16
    Norway 686.4831106 17
    Japan 681.0998676 18
    Italy 559.3157658 19
    India 532.805825 20
    Turkey 523.4718398 21
    Greece 520.4168003 22
    Bolivia 512.1387803 23
    France 454.8243968 24
    Hong Kong 421.0777055 25
    Indonesia 417.3109455 26
    Mexico 334.7095323 27
    Romania 292.8878828 28
    Australia 266.7116148 29
    South Africa 209.3538961 30
    Thailand 206.6588923 31
    Tajikistan 174.4791075 32
    Lithuania 162.452128 33
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 06-29-2011, 09:33 AM #6
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Dynamic Ranking Formula w/ Multiple Criteria

    If you can use that new table.. just changing the column C formula to:

    Please Login or Register  to view this content.
    to make it dynamically choose the right month column...

    Then ranking in G12 would be:

    =VLOOKUP(C9,$B$19:$D$51,3,0)

    Note: You can put the helper table in another sheet or out of visible range if you want to hide it.
    Reply With QuoteRegister To Reply
  7. 06-29-2011, 09:41 AM #7
    nwd9s
    nwd9s is offline
    Registered User
    Join Date
    02-15-2011
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Dynamic Ranking Formula w/ Multiple Criteria

    Hi NBVC-

    Thanks for your help. I will look into using the helper table, will just have to be careful to make sure the list updates as countries are added/removed.

    Do you think there is any way to do this sort of ranking formula without a help table?

    Thanks,
    nwd9s
    Reply With QuoteRegister To Reply
  8. 06-29-2011, 10:15 AM #8
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Dynamic Ranking Formula w/ Multiple Criteria

    Not sure that is an easy task...

    Another possibility could be to add a couple of helper columns to the existing Country Data table... so in E4 of Country Data sheet enter:

    Please Login or Register  to view this content.
    copied down

    in F4 enter:

    Please Login or Register  to view this content.
    copied down

    then in G12 of the Exposure Report:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 06-30-2011, 07:35 AM #9
    nwd9s
    nwd9s is offline
    Registered User
    Join Date
    02-15-2011
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Dynamic Ranking Formula w/ Multiple Criteria

    Thanks, was able to get this working. Originally was trying to avoid having the helper tables, but it seems just to be much easier to incorporate them

    Thanks again!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1