Double nested IF statement formula in Excel

Question

Good morning,

I would like to set a multiple if statement in Excel.

In row 8 you can see the following formula, which gives an answer in column Q. The answer based on this nested formula is Yes or No.

In row 13 all the cells are empty. Because the nested formula has been dragged down it still shows "No".

I was trying to write some double nested IF statement for this, which could cover the situation when the cells are empty and the Q column can come empty as well as a result.

   =IF(OR(M9=" ",OR(N9=" ",OR(O9=" ",OR(P9=" "(IF(OR(M9="No", OR(N9="No", OR(O9="No",OR(P9="No")))))),"Yes","No")," ")

Unfortunately, the formula is not working.

Is it a possibility to add up an additional condition for the nested IF statement in Excel?

Answer 1

As @JvdV has noted in comment above, your latest formula in comment above is still checking for a space, not a null (empty) cell. Writing " " will look for a cell that contains a space (someone has hit spacebar). Writing "" will check to see if a cell contains nothing, in other words check to see it is empty.

I also noticed the error in your re-write: You wrapped the logical test within the parenthesis for the COUNTA formula. This is your formula corrected:

=IF(COUNTA(M9:P9)=0,(OR(M9="No",OR(N9="No",OR(O9="No",OR(P9="No"))))),"")

However, each OR statement gives a TRUE or FALSE result and can handle multiple arguments, as indicated by @SolarMike so I think you could probably re-write your formula to as follows to get the same result, if I have understood your requirements correctly:

=IF(COUNTA(M9:P9)=0,IF(COUNTIF(M9:P9,"No")>0,"Yes","No"),"")

Answer 2

Something like:

=IF(COUNTA(M2:P2),<YourFormula>,"")

Where <YourFormula> is a placeholder for whatever formula you are interested in when any value in M2:P2 has a value.