CODEWITHSUNDEEP
excelworksheet-function
user6621347
user6621347

Reputation:

Workaround for "Formula omits adjacent cells"

In Excel, with the following function:

=IF(AND(N3=1,ISNUMBER(D3),ISNUMBER(E3)),SUM(D3:E3)-2,IF(AND(N3=1,D3="",E3=""),G3,IF(N3=1,"",IF(AND(N3=0,ISNUMBER(D3)),D3-1,IF(AND(N3=0,ISNUMBER(E3)),E3-1,IF(AND(N3=0,D3="",E3=""),G3,IF(N3="","",G3)))))))

I get the error:

Formula omits adjacent cells

How can I fix the formula to avoid getting the error?

Upvotes: 13

Views: 61674

Answers (3)

Jool
Jool

Reputation: 1785

If the cell range you specify is what you intended, i.e. you do want to deliberately exclude certain cells, for example a blank row or column (or even just one cell or more), you can effectively "tell" Excel to ignore those cells by changing their format to text. Excel knows then to not try to include them.

Upvotes: 0

cubs
cubs

Reputation: 39

Just use absolute cell references with the $ sign and this warning will go away:

$A$1 instead of A1

Upvotes: 3

Jonathan
Jonathan

Reputation: 1025

The error you are getting means that there are cells near the ones in your formula that are of a similar format and Excel thinks that you might have missed them by accident. For example, if you had

    A
1 87
2 76
3 109
4 65

then the formula 

=SUM(A1:A3)

would give a similar error. So, without seeing your source data, it is difficult to answer your question. I would recommend updating the formula as it suggests and then seeing if it then includes all the cells you want. If it doesn't, just undo and ignore the error.

Upvotes: 15

Related Questions