That does work, however with the TRUE part in there it was ranking backwards.
I removed it and it returns what I expected, so thanks. I wish I understood
the offset function better.

Thanks,

"Bernie Deitrick" wrote:

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?