Ranking of data in non-continuous rows

[siddharthnk]

Hi,

I need help in ranking data in non-continuous rows. I have two columns. Column A has numbers that need to be ranked. For example we will take the numbers 100, 75, 100, 75 & 50 in that order from cell A1 to A5. The second column has the letters Y or N which will be arrived at based on certain conditions from cell B1 to B5. The ranking only needs to happen on numbers in rows where there is a Y in column B with the highest number getting the 1st rank. If there are identical numbers in column A, these identical numbers should not have the same rank and again no rank should be skipped.
In the above example if the 100's and the 75 have a Y next to them, the ranking should be as follows
100=1st rank, (2nd)100 = 2nd rank, 75 = 3rd rank.

Thanking you in advance

 

[Marcelo Branco]

Maybe...


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Values​

[/TD]
[TD]

Y/N​

[/TD]
[TD]

Rank​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

100​

[/TD]
[TD]

Y​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

75​

[/TD]
[TD]

N​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

100​

[/TD]
[TD]

Y​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

75​

[/TD]
[TD]

Y​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

50​

[/TD]
[TD]

N​

[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


Formula in C2 copied down
=IF(B2="Y",COUNTIFS($A$2:$A$6,">"&A2,$B$2:$B$6,"Y")+COUNTIFS(A$2:A2,A2,B$2:B2,"Y"),"")

M.

 

[siddharthnk]

Thanks Marcelo. This is exactly what I needed. Wishing you a prosperous new year in advance.

 

[Marcelo Branco]

Thanks Marcelo. This is exactly what I needed. Wishing you a prosperous new year in advance.

You are very welcome.

Happy New Year!

M.