Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
299 views
in Technique[技术] by (71.8m points)

"The formula refers to a range" error in Excel

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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)

????????finite termination


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...