Calculating the change in ranking

[JenniferMurphy]

Is there a way to calculate the change in rankings (+/-) in two lists that are sorted by that ranking? That is, the ranked entities are not in the same order in both lists, so I cannot compare one entity with the entity in the same row in the other list.

These are the top ten NFL teams as ranked by one odds-making website. I calculated the values in the +/- column by hand. I'd like to find a way to do it automatically.

[TABLE="width: 651"]
<tbody>[TR]
[TD]Rank[/TD]
[TD]Team[/TD]
[TD="colspan: 2"]Start of Season[/TD]
[TD]Rank[/TD]
[TD]+/-[/TD]
[TD]Team[/TD]
[TD="colspan: 2"]Week 1[/TD]
[/TR]
[TR]
[TD]#1[/TD]
[TD]Denver Broncos[/TD]
[TD]6/1[/TD]
[TD]16.67%[/TD]
[TD]#1[/TD]
[TD]n/c[/TD]
[TD]Seattle Seahawks[/TD]
[TD]4/1[/TD]
[TD]25.00%[/TD]
[/TR]
[TR]
[TD]#1[/TD]
[TD]Seattle Seahawks[/TD]
[TD]6/1[/TD]
[TD]16.67%[/TD]
[TD]#2[/TD]
[TD]n/c[/TD]
[TD]Denver Broncos[/TD]
[TD]11/2[/TD]
[TD]18.18%[/TD]
[/TR]
[TR]
[TD]#3[/TD]
[TD]New England Patriots[/TD]
[TD]8/1[/TD]
[TD]12.50%[/TD]
[TD]#3[/TD]
[TD]n/c[/TD]
[TD]New England Patriots[/TD]
[TD]10/1[/TD]
[TD]10.00%[/TD]
[/TR]
[TR]
[TD]#4[/TD]
[TD]Green Bay Packers[/TD]
[TD]11/1[/TD]
[TD]9.09%[/TD]
[TD]#4[/TD]
[TD]n/c[/TD]
[TD]New Orleans Saints[/TD]
[TD]12/1[/TD]
[TD]8.33%[/TD]
[/TR]
[TR]
[TD]#4[/TD]
[TD]New Orleans Saints[/TD]
[TD]11/1[/TD]
[TD]9.09%[/TD]
[TD]#4[/TD]
[TD]+2[/TD]
[TD]San Francisco 49ers[/TD]
[TD]12/1[/TD]
[TD]8.33%[/TD]
[/TR]
[TR]
[TD]#6[/TD]
[TD]San Francisco 49ers[/TD]
[TD]12/1[/TD]
[TD]8.33%[/TD]
[TD]#6[/TD]
[TD]-2[/TD]
[TD]Green Bay Packers[/TD]
[TD]15/1[/TD]
[TD]6.67%[/TD]
[/TR]
[TR]
[TD]#7[/TD]
[TD]Philadelphia Eagles[/TD]
[TD]20/1[/TD]
[TD]5.00%[/TD]
[TD]#7[/TD]
[TD]n/c[/TD]
[TD]Philadelphia Eagles[/TD]
[TD]20/1[/TD]
[TD]5.00%[/TD]
[/TR]
[TR]
[TD]#8[/TD]
[TD]Chicago Bears[/TD]
[TD]26/1[/TD]
[TD]3.85%[/TD]
[TD]#8[/TD]
[TD]+3[/TD]
[TD]Cincinnati Bengals[/TD]
[TD]25/1[/TD]
[TD]4.00%[/TD]
[/TR]
[TR]
[TD]#9[/TD]
[TD]Indianapolis Colts[/TD]
[TD]28/1[/TD]
[TD]3.57%[/TD]
[TD]#8[/TD]
[TD]+1[/TD]
[TD]Pittsburgh Steelers[/TD]
[TD]25/1[/TD]
[TD]4.00%[/TD]
[/TR]
[TR]
[TD]#9[/TD]
[TD]Pittsburgh Steelers[/TD]
[TD]28/1[/TD]
[TD]3.57%[/TD]
[TD]#10[/TD]
[TD]-1[/TD]
[TD]Indianapolis Colts[/TD]
[TD]30/1[/TD]
[TD]3.33%[/TD]
[/TR]
</tbody>[/TABLE]

I was able to get it done with a multi-step procedure:

1. Sort both lists by team name.
2. Calculate the change now that the teams are on the same row.
3. Convert the results to text.
4. Resort both lists by rank.

This works, but it's a little tedious. Is there a way I can do it on the lists without resorting?

 

[Andrew Poulsom]

With your data in A1:I11 you can use a formula like this to return the previous rank:

=INDEX(A$2:A$11,MATCH(G2,B$2:B$11,FALSE))

 

[JenniferMurphy]

With your data in A1:I11 you can use a formula like this to return the previous rank:

=INDEX(A$2:A$11,MATCH(G2,B$2:B$11,FALSE))

Very nice. Works like a charm. Thank you.

Can this code be executed from a UDF? I'd pass it the arguments, maybe something like this:

Function PrevRank(Entity, EntityList, RankList)

   PrevRank=INDEX(RankList,MATCH(Entity,EntityList,FALSE))

End Function

 

[Andrew Poulsom]

Why would you need a UDF that uses the same functions as the formula I gave you?

 

[JenniferMurphy]

Well...

1. Because the expression is long, obtuse, and typo-prone. A UDF call would be much clearer.
2. Because I might want to change the expression. With a UDF I only have to change it in one place.
3. Because I might want to do some other work, which would require additional arguments.

Is that enough? I can probably think up a few more...

 

[JenniferMurphy]

With your data in A1:I11 you can use a formula like this to return the previous rank:

=INDEX(A$2:A$11,MATCH(G2,B$2:B$11,FALSE))

I did a little research based on your suggestions. I am wondering what the difference is between your solution and this one?

   =LOOKUP(G5,A$5:A$9,B$5:B$9)

They seem to return the same results.

 

[JenniferMurphy]

I did a little research based on your suggestions. I am wondering what the difference is between your solution and this one?

   =LOOKUP(G5,A$5:A$9,B$5:B$9)

They seem to return the same results.

I see the difference now. LookUp requires that the reference list be in ascending order. Your solution works regardless of the order.

Is that correct?

 

[JenniferMurphy]

Here's a little quick and dirty function that works like the vector form of the built-in LookUp function but without the restriction that LookUpVector be sorted.

'========================================================================================
'                          My LookUp Function

' This function does what the built-in LookUp function should have done.
' It works the same way, but does not require that LookupVector be sorted.

' Syntax: =MyLookUp(LookupValue, LookupVector, ResultVector)

'   LookupValue   The value to be looked up in the Lookup vector.
'   LookupVector  The vector (row or column) where the LookupValue will be searched for.
'   ResultVector  The vector where the corresponding value will be found and returned.
'========================================================================================
Function MyLookUp(LookupValue, LookupVector, ResultVector)
Dim LookupIndex
LookupIndex = WorksheetFunction.Match(LookupValue, LookupVector, False)
MyLookUp = WorksheetFunction.Index(ResultVector, LookupIndex)
End Function

I'm sure it needs some clean up, such as declaring the vector parameters "as Range" and adding error handling. But for my needs, it works great.

All comments and suggestions appreciated.