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

--
Kind regards,

Niek Otten

"flow23" wrote in message
...
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...