1

I have "A, B, C and D." I need " if a>0, use a, otherwise, use b. If b has the error "#N/A", use c, otherwise use b. If c=0, use d/61, otherwise use c.

So far, I have this:

=IF(DB11>0,DB11,IF(ISNA(DA11),CZ11,IF(CZ11=0,BE11/61,CZ11)))

Where: DB11 = 0 (A), DA11 = #N/A (B), CZ11 = 138 (C) and BE11 = 6000 (D)

1
  • Maybe this article will help you better understand nesting.
    – Trav
    May 26, 2015 at 20:12

1 Answer 1

1

I think rewording your conditions may help you see where you've gone wrong. Think of it this way instead:

If `a>0`, then `a`. Otherwise, check if `b=#N/A`.
  If `b=#N/A`, then check if `c=0`. Otherwise, `b`.
    If `c=0`, then `d/61`. Otherwise, `c`.

Note the difference between the first line and what you described. You jumped straight to "use b" and then checked that b met a condition after you already said to use it. This won't work in a nested conditional statement. If you conclude something, then it's done; no other conditions are checked.

To illustrate this, consider the case where a=0, b=#N/A, c=0, and d=61. You expect this to result in 1. Evaluating your formula would go as follows:

=IF(0>0,DB11,IF(ISNA(DA11),CZ11,IF(CZ11=0,BE11/61,CZ11)))
=IF(FALSE,DB11,IF(ISNA(DA11),CZ11,IF(CZ11=0,BE11/61,CZ11)))
=IF(ISNA(DA11),CZ11,IF(CZ11=0,BE11/61,CZ11))
=IF(ISNA(#N/A),CZ11,IF(CZ11=0,BE11/61,CZ11))
=IF(TRUE,CZ11,IF(CZ11=0,BE11/61,CZ11))
=CZ11
=0

Since the check of the condition on b concludes with c, the condition on c is never even checked. This is the source of your error.

Here's how your formula should be constructed.

=IF(DB11>0,DB11,IF(ISNA(DA11),IF(CZ11=0,BE11/61,CZ11),DA11))

To illustrate how this works, look at the same example case as above.

=IF(0>0,DB11,IF(ISNA(DA11),IF(CZ11=0,BE11/61,CZ11),DA11))
=IF(FALSE,DB11,IF(ISNA(DA11),IF(CZ11=0,BE11/61,CZ11),DA11))
=IF(ISNA(DA11),IF(CZ11=0,BE11/61,CZ11),DA11)
=IF(ISNA(#N/A),IF(CZ11=0,BE11/61,CZ11),DA11)
=IF(TRUE,IF(CZ11=0,BE11/61,CZ11),DA11)
=IF(CZ11=0,BE11/61,CZ11)
=IF(0=0,BE11/61,CZ11)
=IF(TRUE,BE11/61,CZ11)
=BE11/61
=61/61
=1
1
  • Just a comment: Very often, formulas that are too long/complex to wrap your head around can be simplified by breaking them up and putting intermediate results into additional columns. May 26, 2015 at 21:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .