I am trying to rank all values from two unequal lenth columns. I need to
use the correction factor defined in user help as =[COUNT(ref) + 1 €“
RANK(number, ref, 0) €“ RANK(number, ref, 1)]/2 so that tied values are ranked
appropriately. My "number" value is each entry in each of the two columns.
My "reference" is the array of both columns.

My problem: I want to type this formula once and then CTRL D to copy it
down an entire column lenth corresponding to change in "number" for each
row. But excel is not only advancing the "number" in the formula by one
box, but is also advancing the reference arrays by one box. How do I hold
the reference these same in all rows while advancing the number value
(without having to do it cell-by-cell which will take forever since I have
hundreds of numbers)?

Thanks.

Hi,

To hold part of the formula fixed change the references like:

A10:A100 to A$10:A$100

This will make the row numbers absolute.
--
Cheers,
Shane Devenshire


"W Chamberlain" wrote:

I am trying to rank all values from two unequal lenth columns. I need to
use the correction factor defined in user help as =[COUNT(ref) + 1 €“
RANK(number, ref, 0) €“ RANK(number, ref, 1)]/2 so that tied values are ranked
appropriately. My "number" value is each entry in each of the two columns.
My "reference" is the array of both columns.

My problem: I want to type this formula once and then CTRL D to copy it
down an entire column lenth corresponding to change in "number" for each
row. But excel is not only advancing the "number" in the formula by one
box, but is also advancing the reference arrays by one box. How do I hold
the reference these same in all rows while advancing the number value
(without having to do it cell-by-cell which will take forever since I have
hundreds of numbers)?

Thanks.