rank

[Guest]

Cell A2 needs to rank

formula looks like this =RANK(K2,K:K,0)
Range K:K is defined as "Total"

the problem is every month, a new column is added in between (months) this
pushes total to next column ( say L, M , N)

thus the formula wont work in a months time..

also when the formula is copied down.. using a macro.. The last row also
contains Grand Total. Thus that row is RANK 1... (because its total).

Is there a macro to avoid this.?



Its copies down...

 

[Niek Otten]

I suppose you mean

=RANK(A2,K:K,0)

When you insert columns, the formula will be changed automatically

To avoid looking at the total, don't use K:K, but use K1:K100 (or whatever
your last row is) instead

 

[Guest]

but then rows are added regularly aswell as the columns

and the formula should say a2 = Rank (K2,K:K,0)

 

[Bob Phillips]

Still shouldn't make any difference as Niek says.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

but how would I know how many rows are there as they keep varying....
K1: K100 therefore cant be done

 

[Niek Otten]

OK, leave it as K:K. Use Rank to find the second largest item