logic of $A$1:A1

[R.VENKATARAMAN]

I am fairly familiar with excel and vbe.

But the logice of this deludes me

my values in A1 to A7 are
rr
rv
rs
ra
va
vs
vd
in B1 if I type
=COUNTIF($A$1:A1,"r"&"*")
I get 1
if I drag B1 down to COPY the formula to B2 to B7 this gives 1 in each cell
In B7 the formula as seen in the formula bar is
=COUNTIF($A$1:A7,"r"&"*")

but suppose in some other cell say E1if I type the forumula
=COUNTIF($A$1:A7,"r"&"*")-------same as in B7
the result is 4 which is correct.

why this difference in B7 and E1

what is meant by <$A$!:A1) in B1

your valuable commens please

 

[Alan]

I am fairly familiar with excel and vbe.

But the logice of this deludes me

my values in A1 to A7 are
rr
rv
rs
ra
va
vs
vd
in B1 if I type
=COUNTIF($A$1:A1,"r"&"*")
I get 1
if I drag B1 down to COPY the formula to B2 to B7 this gives 1 in
each cell In B7 the formula as seen in the formula bar is
=COUNTIF($A$1:A7,"r"&"*")

but suppose in some other cell say E1if I type the forumula
=COUNTIF($A$1:A7,"r"&"*")-------same as in B7
the result is 4 which is correct.

why this difference in B7 and E1

what is meant by <$A$!:A1) in B1

your valuable commens please

Hi,

In Excel 2000 I get the following results in B1:B7:

1
2
3
4
4
4
4


Are you sure you have automatic calculation turned on? Or perhaps
press F9 to re-calc after copying down.

HTH,

Alan.

 

[Frank Kabel]

Hi
goto 'Tools - Options - Calculate' and enable automatic calculation

 

[R.VENKATARAMAN]

thanks. that could be the problem
that brings me to another problem when I go to tools-options-calculate and
check automatic calculation and click ok it is ok for that workbook. If I
close excel and again open a new workbook the calculation is not automatic.

how to make calculation automatic permanently.-i.e automatic as default

 

[Jerry W. Lewis]

Alan and Frank have covered why your formula is not recalculating. As
for your question regarding the meaning of $A$1:A1, it refers simply to
cell A1, but copies differently. $A$1 is anchored at the upper left
corner, but :A1 will adjust relative to the the destination cell in a
copy/paste operation.

Jerry