Both INDEX and RANK_UNIQUE return row numbers. Latter sorts by the field you give it e.g. RANK_UNIQUE(SUM(Sales)) sorts by sum of sales and in the process drops nulls and densified rows. But when you make it RANK_UNIQUE(ZN(SUM(Sales))) you get rid of that behavior so it will start counting everything like INDEX(). Then the only difference left is sort order. Go to table calc settings for INDEX() and set sort by SUM(Sales) to get the same results. There is nothing slippery about either function as long as you know how they behave.
The reason we built the RANK functions this way is because nulls and densified rows would otherwise skew the results which would be bad especially for a function like rank_percentile.
Everything is relative. When I say slippery, I mean Index() may turn out
different numbers for different partitions and addressings because
it involves table calculations. One must be extra careful when using
Index() and get the settings right.
Rank_Unique() is easy to use and easy to understand. In many cases, it
requires less steps.
On Mon, May 11, 2015 at 10:45 AM, Bora Beran <
RANK functions are certainly much easier to use since sorting is built into them since we can safely assume if you are ranking sum(sales) you want the rank to operate on data sorted by sum(sales). And since in most cases, people sort on things like sales, profit etc. we also default to descending sort. Good defaults make it really easy to use RANK calculations. But INDEX and RANK are both table calcs and will give different results based on different addressing/partitioning settings.
Alexander Mou Bora Beran I got stuck with another Rank / Index problem. In this case, I have a duplicate, and when I filter the duplicate, the Index doesn't capture that filter. (I tried different calculations but same problem.)
Please see workbook for example. In the workbook, the problem I'm having is it skips over rank #3, which is filtered for by the duplicate. So it goes - 1, 2, 4.......instead of 1, 2, 3.
Duplicates Ranking Problem.twbx 30.8 KB
Could you start a new thread? this is a different question.
On Tue, May 12, 2015 at 2:35 PM, Chris Pham <
It worked with my data. Thank you very much!