Search code examples
excelworksheet-function

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?

Solution

  • 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.