Calculating Rank

3.10K 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!


You are viewing 1 out of 3 answers, click here to view all answers.