=IF(H181=0,A181,IF(AND(F18

Thanks

Solved

Posted on 2014-07-23

Why is the following formula not returning blank if not equal to any of the conditions?

=IF(H181=0,A181,IF(AND(F181=1,H181="LAM"),A181&$M$2,IF(AND(F181=2,H181="LAM"),A181&$M$3,IF(AND(F181=3,H181="LAM"),A181&$M$4))))

=IF(H181=0,A181,IF(AND(F18

10 Comments

=IF(H181=0,A181,IF(AND(F18

Thanks

=IF(

Thanks

Change Cell H181 to text Format

Let's break out your nested IF statement to see what's happening:

=IF(H181=0

True: A181

False:IF(AND(F181=1,H181="

True: A181&$M$2,

False: IF(AND(F181=2,H181="LAM"),

True: A181&$M$3,

False: IF(AND(F181=3,H181="LAM"),

True: A181&$M$4))))

False:

You have no final False result to show for the final test.

If H181 is blank or zero, then the value of A181 results

If H181 is equal to "LAM" then the concatenation of A181 and either M2, M3, or M4 (depending on the value of F181) results

If H181 is anything else then FALSE results (since you provided no result for this possible condition)

If you want the formula to result in a blank if H181 is indeed blank and not zero, then you'd need to add another test to the statement. I assume you want the value of A181 to result if H181 is zero, so:

Note that I've added a result of "N/A" if none of the subsequent tests pass (ex., F181 is not 1-3, H181 is neither blank, zero, or "LAM")

Regards,

-Glenn

PS I highly recommend using the Evaluate Formula tool (Menu: Formulas, Formula Auditing section, Evaluate Formula). It really helps debugging complex formulas like this.

120 characters vs. 147

PPPS :-)

107 characters. :-)

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Counting # of WOs in Excel meeting specific criteria | 5 | 19 | |

Time Clock in Excel issues | 5 | 26 | |

In Excel how to add formula for Quintile? | 4 | 23 | |

MS Access Form Control Background Color Change Depending On How Long The String Text Length Is | 18 | 36 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**8** Experts available now in Live!