Workaround for "Formula omits adjacent cells"

excel worksheet-function
user6621347 picture user6621347 · Oct 15, 2016 · Viewed 43.3k times · Source

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?

Answer

Jonathan picture Jonathan · Oct 15, 2016

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.

Related questions

IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
Get values from other sheet using VBA

I want to get values from other sheets. I have some values in Excel (sheet2) for example: A B C D - - - - 1 | 2 5 9 12 2 | 5 8 4 5 3 | 3 1 2 6 I sum each column in row 4. I'm working with these values in sheet2 but I …

Read More
Excel formula to get cell color

I would like to know if we can find out the Color of the CELL with the help of any inline formula (without using any macros) I'm using Home User Office package 2010.

Read More