“The formula refers to a range” error in Excel

Question

Am new to Excel, please help me with this:

    A      B
2   9     =IF(A2:A6>=7,"1","0")
3   4
4   7
5   4
6   5

For B2 the formula works perfectly fine but in B3 it selects from A3:A6. I know the concept of Absolute referencing. But here in this example it should basically work without any errors, right?

    A      B    c
2   9     1    =Sum(A2:B2)
3   4     2
4   7    33
5   4     3
6   5    22

The above example works fine. What is the difference between two?

Answer 1

If you want the A2:A6 range to float down as you copy the formula to other rows, use:

=IF(MAX(A2:A6)>=7, 1, 0)

However, if you want row 6 locked as the finite terminator of the range then lock it as absolute with a $ like this,

=IF(MAX(A2:A$6)>=7, 1, 0)