Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts

    Unhappy cannot get proper sorting

    Hello everyone;

    I need help.

    Attachment is a spreadsheet where table A list symbols of stock and their corresponding prices.

    In table B, I tried to assign to them a ranking according to increasing price.

    My problem is that the formula I created doesn't work when two stocks have the SAME price.

    Please notice that BNS.PR.L and TD.PR.O both quoted 25.25 , but table B returned repeatedly BNS.PR.L ; disregarding TD.PR.O (highlighted cells illustrate this)

    I tried to used Vlookup (that offers an EXACT match), but cannot get a satisfactory result either (probably I am not using it correctly).

    Any helps, suggestion, assistance will be very much appreciated.

    Thanks in advance


    Daniel Rozenberg
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    To adjust for dups, you need a countif in the formula. See the attached.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts
    hi kweaver;

    thank you very much for you help.

    i am not familiar with the INDEX function, so i will "chew & digest" on your solution, and will get back when more acquainted.

    THANKS again


    daniel rozenberg.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Thank of indexing as sub-scripting an array for a (row, col)
    Last edited by kweaver; 2013-09-20 at 20:14.

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Toronto, CANADA.
    Posts
    292
    Thanks
    21
    Thanked 3 Times in 3 Posts
    hi kweaver;

    i printed the help for rank, and the two formats of index commands.
    read them (several times, until properly digested, i think), and now i feel much more confident to what i want to do.
    in fact what i sent in original post is an extract of larger sheet; my intend is more ambitious.
    THANK YOU VERY MUCH again, for pointing to me the use of those commands.

    daniel rozenberg.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •