Nested IF AND statements in Excel

Posted on 2013-05-13
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?
Question by:pstiffsae
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 23

Accepted Solution

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


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

Author Comment

ID: 39162508

Author Comment

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?
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 23

Expert Comment

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

Author Comment

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!
LVL 23

Expert Comment

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

Author Comment

ID: 39162593
Thanks again!!!!

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

623 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