cannot get proper sorting

Topic

New Reply

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

Reply | Quote

Replies

To adjust for dups, you need a countif in the formula. See the attached.

Reply | Quote

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.

Reply | Quote

Thank of indexing as sub-scripting an array for a (row, col)

Reply | Quote

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.

Reply | Quote