?
Solved

Nested IF AND statements in Excel

Posted on 2013-05-13
7
Medium Priority
?
392 Views
Last Modified: 2013-05-13
I'm have a bit of trouble with a IF AND statement in Excel:

=IF(AND(J55="Lodge or dorm floor",J62="Yes"),F11*-0.2,IF(AND(J62="Yes",J55="House"),F11*-0.4),IF(J62="No",F11+0))

Long and the short:
IF J55 = Lodge or dorm AND j62 = Yes THEN F11*-0.2
IF J55 = House and J62 = YES THEN F11*-0.4

This works - but when I want to show a No field for J62 (or even a blank) the equation is broken and shows FALSE. When someone selects NO, I'd like nothing to happen.

What am I doing wrong?
0
Comment
Question by:pstiffsae
  • 4
  • 3
7 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 2000 total points
ID: 39162496
Maybe need to remove a closing bracket...

Try:

=IF(AND(J55="Lodge or dorm floor",J62="Yes"),F11*-0.2,IF(AND(J62="Yes",J55="House"),F11*-0.4,IF(J62="No",F11+0,"")))
0
 

Author Comment

by:pstiffsae
ID: 39162508
PERFECT!!!
0
 

Author Comment

by:pstiffsae
ID: 39162534
Sorry - If nothing is entered, I still get false - how can I leave the cell empty and not show FALSE in the Function?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 23

Expert Comment

by:NBVC
ID: 39162538
You shouldn't get FALSE with my formula.  You should get a blank if nothing has been entered in the other cells.
0
 

Author Comment

by:pstiffsae
ID: 39162561
When I throw in your formula, it's perfect. When I initially put in information into the cells to satisfy the formula, it works perfect. When I clear our the values, it' returns a FALSE. What am I doing wrong?

NOPE - you're right - works perfectly!
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39162591
Ok.. see that you got it.  Great!
0
 

Author Comment

by:pstiffsae
ID: 39162593
Thanks again!!!!
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In this article, I will demonstrate that how to do a PST migration from Exchange Server to Office 365. This method allows importing one single PST, or multiple PST's at once.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

621 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