reference for a cell in the same column

[edolgonos]

how can I compare data from the same column and differnt rows?

for example I want to compare a1 to a2, and set B2 =1, if a1=a2, and b2=2, otherwise.
I can do it with formula in b2: =if(a1=a2,1,2).
Now I can copy this formula for cells b3 thru b15.

the problem arises, when one row is added, or deleted in the middle of the rows.

my question is : is it possible to refernce a2, as the a(current row +1)? or a3 as a(current row -1)

so my formula would be =if(a(current row) = a(currentrow-1), 1,2)

[rylo]

Hi

Try

=IF(INDIRECT("A"&ROW())=INDIRECT("A"&ROW()-1),1,2)


rylo

[gajendra.gupta]

Hi,

If you are using VBA then you may write it as well.

If ActiveCell.Value=ActiveCell.Offset(1,0).Value Then
B=1
Else
B=2
End If

Regards,
Gajendra Gupta

[edolgonos]

Hi

Try

=IF(INDIRECT("A"&ROW())=INDIRECT("A"&ROW()-1),1,2)


rylo

Thank you very much! it worked!
and if you govorish' po russki- bol'shoe spasibo, rylo (morda(?), litso, punim)