Using Rank formula with non consecutive rows

[greykitten]

Hello,
I'm trying to use the rank formula to retrieve the three largest values. However, the data that needs to be ranked is entered with a row in between each value. I can't figure out how the rank formula should be written (if even possible) to recognize and rank values on non-consecutive rows. I tried this formula =RANK(C20:C20,C22:C22,0)+COUNTIF(C20:C20,C22:C22,C20) with the C20:C20, C22:C22, etc representing single cells. My data looks like this below with a row in between. Thank you in advance for your help. I really appreciate it.

Thanks again,
grey kitten Value Rank
250 4

380 3

450 2

500 1

 

[konew1]

Try this, if the rows inbetween your data are empty

Excel Workbook
	A	B
1	250	4
2
3	380	3
4
5	450	2
6
7	500	1
Sheet1

 

[barry houdini]

Hello greykitten, welcome to MrExcel

Are the rows in between all blanks? If so then try this formula in row 20 copied down

=IF(C20="","",RANK(C20,C$20:C$26)+COUNTIF(C$20:C20,C20)-1)

 

[greykitten]

Ordinarily, that would work but I have other unrelated data in between the rows that I need to exclude. Below, the smaller numbers between 100-500 are the ones I need to rank and I need a formula to exclude the huge numbers in between.
[TABLE="width: 157"]
<TBODY>[TR]
[TD]Rank Values
4 100
$456,988
3 150
$1,234,222
2 200
$567,982
1 500


Thank you so much,
greykitten</SPAN>[/TD]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] </SPAN>[/TD]
[/TR]
[TR]
[TD] </SPAN>[/TD]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD] </SPAN>[/TD]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[TD]</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=2></COLGROUP>[/TABLE]

 

[barry houdini]

You can use RANK with a "union" instead of a range, so this formula in row 20

=RANK(C20,(C$20,C$22,C$24,C$26))

leave row 21 blank then select both of those cells (one with a the formula, one with the blank) and copy both down