Excel Rank Formula Help

[Steve Voltage]

The below formula is placed in C1
=RANK(H4,$H$4:$H$11,1)

How can i make the above formula so the the ref. cells are determined by A4:B4,A5:B5,A6:B6 etc?

so for example if i have $H$7 in A4 and $H$15 in B4 then this is what the ref cell will be. Not sure if the above makes sense but here goes

[KevinTHFC]

I think this might help http://www.cpearson.com/excel/indirect.htm

[osknows]

Hello Arnold,

I think you need something like this

=RANK(INDIRECT(A4),INDIRECT(A4&":"&B4),1)

where A4 & B4 are cell references
A4 = $H$7
B4 = $H$15

The formula above effectively builds this formula
=RANK($H$7,$H$7:$H$15,1)


All the best
Os