Solved

# Formula help

Posted on 2014-07-23
121 Views
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))))
0
Question by:RWayneH

LVL 8

Assisted Solution

Try this

=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,""))))

Thanks
0

LVL 8

Expert Comment

Perhaps

=IF(H181="",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,""))))

Thanks
0

LVL 8

Expert Comment

Actually 0 or "" excel consider both one and the same.
0

Author Comment

but I need it to do two different things.  If zero do this, if not, blank??  If "" and 0 are considered the same, how do I do this?
0

LVL 8

Expert Comment

Change  Cell Format to Text - For References.
0

LVL 8

Expert Comment

=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,""))))

Change Cell H181 to text Format
0

LVL 27

Accepted Solution

Excel considers blank cells and zero-value cells differently, depending on how you're testing or applying them.

Let's break out your nested IF statement to see what's happening:
=IF(H181=0
True: A181
False:IF(AND(F181=1,H181="LAM"),
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:
=IF(H181="","",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,"N/A")))))

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.
0

LVL 8

Expert Comment

Thumbs Up Mr.Glenn Ray....   :) :) :)

Thanks
0

LVL 27

Assisted Solution

PPS  The whole formula I provided could also be composed like this:
=IF(H181="","",IF(H181=0,A181,IF(H181="LAM",IF(F181=1,A181&\$M\$2,IF(F181=2,A181&\$M\$3,IF(F181=3,A181&\$M\$4,"N/A"))),"N/A")))

120 characters vs. 147

PPPS :-)
IF(H181="","",IF(H181=0,A181,IF(H181="LAM",IF(AND(F181>=1,F181<=3),A181&OFFSET(\$M\$1,F181,0),"N/A"),"N/A")))

107 characters. :-)
0

Author Closing Comment

EXCELent!!  All good solutions and worked great.  Thanks.
0

## Featured Post

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.