What is the formula for "IF" "AND" Combo?

Bright01
Bright01 used Ask the Experts™
on

EE Professionals,  

How do I combine an If and And Statement to solve the following;

If I have two elements looking for a product:
A1           B1            C1
500       .20             100

If A1 <>0 and B1=0, then C=A1
If A1 = 0 then C= A1
If A1 <>0 and B1<>0, then C1=A1*B1

What is the Fx?

Thank you,

B.


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008
Commented:
=a1*if(B1=0,1,B1)
Top Expert 2010
Commented:
=IF(A1=0,0,IF(B1=0,A1,A1*B1))

Note that if A1=0, then passing A1 or 0 in the first "if true" result do the exact same thing.
HainKurtSr. System Analyst

Commented:
try

=if(and(A1<>0,B1=0), A1, if(A1=0, 0, A1*B1))
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Top Expert 2008

Commented:
And for reference, we're not EE Professionals, we're EE volunteers :)

Thomas
Sr. System Analyst
Commented:
actually this is one to one conversion of what you say:

=if(and(A1<>0,B1=0), A1, if(A1=0, A1, A1*B1))

but there may be shorter versions doing the same thing posted above...
You need to specify a value / condition in case none of the given criteria matches

Put this formula in C1 (I have used "NOTHING SATISFIES" if all conditions are false - for formulas requirement :)) :

=IF(A1=0,A1,IF(AND(A1<>0,B1=0),A1,IF(AND(A1<>0,B1<>0),A1*B1,"NOTHING SATISFIES")))

Regards.
softpro2k, Can you give a set of values for which "NOTHING SATISFIES"
Top Expert 2010

Commented:
Saqib,

We both know s/he can't :)

Thomas: very elegant, that...

Patrick
HainKurtSr. System Analyst

Commented:
:) softpro2k, there are 3 possibilities not 4...

1. A = 0 (you seperated this condition into 2)
2. A != 0, B = 0
3. A != 0, B !=0
Top Expert 2008

Commented:
Thanks Patrick.

T
No doubt Thomas's is a beauty. I was thinking on similar terms but not so compact.
Top Expert 2008

Commented:
I guess beauty doesn't sell ;-)

T
Top Expert 2010
Commented:
Bright01,

I am glad to have helped, but given that there are several ways to accomplish this one, I am puzzled that you ignored the first response with a correct answer in your split.

I would have recommended going with a split of:

http:#a36330089
http:#a36330090
http:#a36330131

Patrick
HainKurtSr. System Analyst

Commented:
Aggreed, I am not happy to get the lion share :) max I should get 250!
Top Expert 2008

Commented:
I understand why the asker went that way: if you want to learn the structure with the nested ifs, Patrick's and HainKurt's formulas are more manageable, so more maintainable and reproduceable.  

T

PS: but my way is prettier!

Author

Commented:
Patrick,

The reason I split it the way I did is that I needed a solution that combined and referenced an IF and an AND statement.  I think, in rereading it, I wasn't clear enough on my requirements. Sometimes when I author a question to EE, I try to keep it simple but on the implementation end, the formula needs to fit a larger set of requirements (if I exposed the larger requirements would probably be inappropriate for EE).

 There were multiple solutions to this problem but as in life, one solution may "fit" better then another.  The one that worked the best for me as I integrated it, was HainKurt's. It's as if three golfers shot for the hole and all hit the green; however, only one was closest to the hole.  If you think I have errored here, I'll split it differently.  

B.
Top Expert 2010

Commented:
Bright01,

You may have been curious about incorporating the AND() function, but Thomas's shorter, more elegant formula does the exact same thing that mine and HainKurt's formulae do.

I'm not trying to say you did anything wrong.  I'm just pointing out that Thomas's suggestion was in fact correct (by "correct" I mean "it generates the correct output for any conceivable range of inputs") and generates the same output that the other ones do.

Patrick

Author

Commented:
Patrick,

I agree!  Given everyone's comments, how do I go back and reallocate appropriately?

Thank you,

B.
Top Expert 2010

Commented:
Bright01,

Normally you would click the Request Attention link and ask the Mods to reopen the question.  I did that a little while, though, so there is no need to do that here.

:)

Patrick

Author

Commented:
Thank you guys!  It was great learning from each of you.........You guys are great EE Professionals, and you do very good volunteer work!

Best regards,

B.
@ partik & @ ssaqibh : Please go through my post before giving your views :

... for formulas requirement :)


Although my formula is working, nutsch has provided the shortest & clever formula. Great !!
softpro2k

You have presented a redundant condition for "NOTHING SATISFIES". This condition can never occur.

Saqib
Reduced another 2 characters

=A1*((B1=0)+B1)
@Saqib: I know this situation will never occur. It was needed for the formula that I used.

I repeat 'Please go through my post before giving your views : "  ... for formulas requirement  "

Your two character reduction is appreciated. Thanks.

Top Expert 2008

Commented:
Good one ssaquibh!

T

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial