Ranking a moving list

[WBTKbeezy]

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?

 

[Bernie Deitrick]

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,COUNT($A$2:$A$100)-1,1),TRUE),"")

and copy down to B100.

HTH,
Bernie
MS Excel MVP

 

[WBTKbeezy]

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]

That does work, however with the TRUE part in there it was ranking backwards.

That's why I said "a formula like this" ;-)

Offset simply returns a range offset from the reference cell by a number of rows, columns, with a
number of rows, and columns in it.

OFFSET($A$2,0,0,COUNT($A$2:$A$100)-1,1)

means "give me a range that is offset from cell A2 by 0 rows and 0 columns (ie, a range that starts
at cell A2), and has one less row in it as there are values in A2:A100, that is one column wide."

The above example returns a range that starts in A2 and has all the cells in A2:A100 that are
filled, except the last one... the tricky part is figuring out where to put $s to make sure that the
formula copies correctly....

HTH,
Bernie
MS Excel MVP