Xcelsius Dynamic Ranking Logic

by Ryan Goodman

I often get questions about dynamic raking within Crystal Xcelsius models so I have put together some lightweight logic that will enable you to rank up to several hundred rows of data during runtime. First we will look at the basic functions needed; then we will combine them into a simple model. Finally, I have provided a more complex model to show you how to generate some more compelling real time analysis using these functions.
LARGE and SMALLMATCHVLOOKUP
With an understanding of how these functions work, let’s combine them to create our dynamic ranking logic.

1. Index cell: This index cell will be used eventually by a vlookup function once the rank is identified
2. Source Data: This is the original source data that we will rank.
3. Unique Identifier. The unique identifier addresses the issue of duplicate values. By carrying the value to the 100 thousandth place, this identifier will ensure that all values in your rank order are unique without affecting the values themselves. This is important because the logic will break down if there are duplicate values
4. Adjusted: The adjusted values will be the range that we will actually perform the LARGE function. Because we have summed the original value and the unique identifier, we know that there are no duplicate values.
   
5. Rank Number: We will use this rank number as our “K” within the LARGE function.
6. Rank Lookup: This range uses the LARGE function to find the Nth value (#5) within our Lookup Row (#4)
7. Match Row: Now that we know the Nth value we need to find the absolute row for which this value is located in the source data (#2).
8. Finally, with the absolute row identified (#7), we can perform a VLOOKUP of this absolute row to return any other information we desire. In this case, it was the name.
   Now that you have the basics down you can check out this more advanced example and reverse engineer the source file. Let me know how this works for you or if you are able to build on this.