Calculating Rank

3.16K viewsDataLink

Ok, say I have a list of 50 states, all ranked according to different metrics. Where each state’s rank changes from list to list.

What I need to do is this:

First, create a matrix with a list of all 50 states. Then, based on the name of the state, will look in each list, and add up the appropriate rank. For example:

Nevada is ranked 1,5,7,15 on four lists. The final matrix will look in each list and add up the above numbers. I have attached an example model (very simple). Is there a formula that can do this? Tks!


This procedure should work:

1. Import the states into the first matrix as you have done.
2. Create the second matrix and link in the state category to this new matrix.
3. Import the new data into the second matrix. You would choose the option to import into an existing matrix, specifying the second matrix you just created. (instead of creating a new matrix).
4. On the panel where you specify categories, you would specify that the ‘state’ column is a type of ‘category’ and you would import this category into the existing ‘state’ category you just linked into matrix 2.

For the third matrix, you would repeat steps 2-4.

Then the formula on the file I posted should work slick. Hope this helps!




Thanks for your reply. Unfortunately, the original tables were built from importing data for each Rank1, Rank2, etc. through the DataLink. So it was not possible for me to link the state category, unless there is a way to do it after the fact. I do know that the list of states is the same, even if in different order.

Barring the option of a linked state category, is there a way to add up the rankings?


This is a lot easier if the data will allow you to link the state category across all the matrices.

As you can see in the attached model – if the states are linked then you can do a straight-forward formula to sum all the states ranks across the matrices.

Then I created a view to do a ASC or DESC sort of the summary view.