There is one very simple function that can be used in order to find the top or lowest ranked line of a data set. If you rememebr the post I had written a macro to capture stock prices, you can easily imagine how you would get a data set as the one below:
Basically, a list of stocks and the price they are currently trading at. If you wanted to get the highest prices, you could do that by using the “rank” function in excel. First off, you need a free column such as the one on the left, then you could simply use in cell A2:
=RANK(C2,C:C)
I then dragged this formula in order to get the ranking for each stock. Once that is done, I can simply create myself a small table such as the one below:
In order to update the table, I can simply update the prices. How? I used vlookup functions for the stocks and prices, as you can see here.
=VLOOKUP(L8,A:C,2,FALSE)
=VLOOKUP(L8,A:C,3,FALSE)
One possible Issue
There is one possible problem here. What if two stocks had the exact same price? Let’s try changing the price of BIDU from 125.26 to 189.32 and see the new result:
As you can see, I get some errors. That might be something that you want but in most cases, it’s far from ideal. How do I resolve such situations? I simply add a small number to the price, just enough to avoid any possible “ties”. Let’s take a look at my 2 new columns:
After adjusting my “rank” formulas to use the new “adjusted price”, you can see my new rankings:
This helps me avoid any errors in my rankings.
As always, you can download our spreadsheet here. Let me know if you have any questions or comments on the rank function.
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
