PASTING FORMULAS

[FLKulchar]

I would like help on the following:

copying a cell "formula"..pasting it to several
cells..WITHOUT changing the array of the cell in the
subsequent fields...

for example,

if I type =rank(a1, a1:a25)...i would like to know the
rank of cell A1.

Suppose I type this in cell B1...then copy and paste it
to cells B1 through B25.

B2 will look as follows:

=rank(a2,a2:a26)

BUT I WISH TO SEE IT AS =rank(a2,a1:a25)

HOW DO I KEEP THE RANGE REFERENCE FROM INCREASING???

thanks,

Larry

 

[Frank Kabel]

Hi Larry
you have to use absolute references. They are indicated by the sign
"$". Absolute references are not changed during copy. So in your
example you should use the following
=RANK(A1,$A$1:$A$25)

you can toggle between absolute and relative references by hitting F4

HTH
Frank

 

[dancing fish]

Larry,

If put your cursor next to a1 in a1:a25 then hit F4 it will insert two
dollar signs liek this $a$1. This makes this cell reference fixed, and
will not change as you copy the formula to other cells. You will have
to do the same to the other part so it looks like this:

=rank(a2,$a$1:$a$25)

If you dont want to use f4 you can type these in manually.
Alternatively, you can use named ranges. Basically, to do this, you
highlight the cells you want, then click in the box next to the formula
box just above cell a1. Then type in a name, such as larry.

then you can sub this into the formula:

=rank(a2,larry)

Hope this helps.
df.