[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Formula help

Posted on 2014-07-23
10
Medium Priority
?
123 Views
Last Modified: 2014-07-24
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
Comment
Question by:RWayneH
  • 6
  • 2
  • 2
10 Comments
 
LVL 8

Assisted Solution

by:Naresh Patel
Naresh Patel earned 668 total points
ID: 40215390
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

by:Naresh Patel
ID: 40215405
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

by:Naresh Patel
ID: 40215414
Actually 0 or "" excel consider both one and the same.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:RWayneH
ID: 40215422
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

by:Naresh Patel
ID: 40215452
Change  Cell Format to Text - For References.
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 40215462
=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

by:
Glenn Ray earned 1332 total points
ID: 40215491
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
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 40215499
Thumbs Up Mr.Glenn Ray....   :) :) :)

Thanks
0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 1332 total points
ID: 40215501
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

by:RWayneH
ID: 40217957
EXCELent!!  All good solutions and worked great.  Thanks.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

834 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question