Ranking by comparing columns

[Erick Celaya]

Hi Everyone,

pretty amateur excel user, hope somebody can help me out. I am trying to input some kind of formula that can solve the following

Problem:

there are 3 columns A: Rank1 B: Rank2 C: COUNT. The idea is to compare the value in Rank 1 cell with the values in the rest of Vector Rank1. If the value in the comparison is equal than I need to somehow compare the subsequent values in the same rows but now in rank 2. Such condition happens when comparing A1(=1) with A5(=1) in this case I need to compare values B1(=1) with B5(=4). If B1 is greater than B5 than COUNT will increase by 1. Such is the case for C5 in which the conditional B5 is greater than B1 hence the count increases. Numbers in the rank columns can be the same hence the comparison might need to happen several times. There might be 3 "1´s" in Rank 1 hence the will be a count of 2 or more. Such situation occurs in the comparison B4 with B1 and B5 where in both cases B4 is greater hence count ++ happens twice.


[TABLE="width: 500"]
<tbody>[TR]
[TD]RANK1[/TD]
[TD]RANK2[/TD]
[TD]COUNT[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Im having a hard time keeping track of the cell thats being compared. Tried using lookup functions but seem limited to working in ascending order. Any feedback would be greatly appreciated.

Thanks!