With your list of data starting in cell A2, put a formula like this into B2:

=IF(COUNT(A2:A$100)1,RANK(A2,OFFSET($A$2,0,0,COUN T($A$2:$A$100)-1,1),TRUE),"")

and copy down to B100.

HTH,
Bernie
MS Excel MVP


"WBTKbeezy" wrote in message
...
I have an issue with ranking a dynamic range. I have a list of data that I
need to rank, but each week the length of the list changes. The regular rank
formula would work, but I have another caveat, the last item in this random
length list needs to be excluded from ranking. So one week I may have 20
items to rank while excluding item # 20, but the next week I may have 24
items to rank excluding item #24. I would rather not update the formula
every week. I feel offset may work, but I am unfamiliar with that function.
Can anyone shed some light on this issue?