Formula help

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))))
RWayneHAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Naresh PatelTraderCommented:
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
Naresh PatelTraderCommented:
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
Naresh PatelTraderCommented:
Actually 0 or "" excel consider both one and the same.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

RWayneHAuthor Commented:
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
Naresh PatelTraderCommented:
Change  Cell Format to Text - For References.
0
Naresh PatelTraderCommented:
=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
Glenn RayExcel VBA DeveloperCommented:
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.
evaluate formula
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Naresh PatelTraderCommented:
Thumbs Up Mr.Glenn Ray....   :) :) :)

Thanks
0
Glenn RayExcel VBA DeveloperCommented:
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
RWayneHAuthor Commented:
EXCELent!!  All good solutions and worked great.  Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.