formula auto adapt to change in rows

[John2007]

I have a simple exel formula that i would like to have it adapt when the number of rows change in my data.

Please Login or Register  to view this content.

the .82 is formula is =1-(6*E9/(COUNT(E2:E8)*(COUNT(E2:E8)*COUNT(E2:E8)-1))).

Is there away create a macro that would
1) take the differnce between Rank 1 and rank2 (diff Column).
2) Square that differnce (column sqre).
3) sum that column ie 10.
4) then plug those variables into the formula.
Any help would be great
john

[davesexcel]

I believe you are looking for dynamic range for one part of the question,
here's one way to do it
http://www.contextures.com/xlNames01.html#Dynamic

[John2007]

Thanks for the help : )