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.

Rank Values 4 100 $456,988 3 150 $1,234,222 2 200 $567,982 1 500 Thank you so much, greykitten</SPAN>	</SPAN>
	</SPAN>
</SPAN>	</SPAN>
</SPAN>	</SPAN>
</SPAN>	</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=2></COLGROUP>

 

[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