Excel: Nested if statement

Question

Working in Excel 2011 and I can't seem to get a few nested if statements to work. It is pretty straightforward:

What I'm trying to say, as also seen in the picture below, is:

if a2 and b2 and c2 all have values then: Shared
if either a2 or b2 have values and c2 has a value then: Shared
if a2 and b2 are both #N/A then: CAN
if c2 if #N/A then: US

my current if statement, shown in picture, is not completed but still doesn't work on the parts that are finished. For example, it does not list CAN for any outputs(rows 12-32). The equation that is highlighted in the picture is for cell d4.

Any help would be much appreciated. Cheers ![enter image description here ]1

Excel will try and work through the formula in order. First you are checking for an error in column C, which is fine, but you are then testing a logical expression on columns A, B and C. Because columns A and B have an error, the calculation throws an error at this point. You may just need to re-order the formula.... — Dave
– Dave, Commented Jul 7, 2016 at 13:08

l1b3rty · Accepted Answer · 2016-07-07 13:13:48Z

1

You need to check for #N/A values first in your IF statement otherwise the evaluation will be stopped and #N/A returned (like in your example)

answered Jul 7, 2016 at 13:13

l1b3rty

3,68019 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

57JWL Over a year ago

Thanks for your reply, it has been very helpful. However, there still seems to be a problem with one part no matter how I rearrange the if statement. My current equation is as follows: =IF(ISNA(C2),"US",IF(AND(ISNA(A2),ISNA(B2)),"CAN",IF(AND(A2="0034",ISNA(B2),C2="0596"),"Shared",IF(AND(ISNA(A2),B2="0052",C2="0596"),"Shared",IF(AND(A2="0034",B2="0052",C2="0596"),"Shared","-"))))) The "#N/A' that I receive now the part where just a2 is '#N/A' and values are there for the other two cells. However, when I move that one, and put it in 3rd in the order for example,

57JWL Over a year ago

it will subsequently come up with a '#N/A' for the part where it says just b2 is '#N/A'. Therefore, I'm very unsure what the problem is because I've mixed the order, which has helped. But I've tried every order and the entire equation still won't work.

l1b3rty Over a year ago

You need to check for #N/A on the A, B, and C columns independently. You do that for C, but you have to do it for A and B also. If A2=#N/A and B2 has some value then "AND(A2="0034",ISNA(B2),C2="‌0596")" will be evaluated which will throw #N/A.

Collectives™ on Stack Overflow

Excel: Nested if statement

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related