Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Nested IF AND statements in Excel

Posted on 2013-05-13
7
Medium Priority
?
385 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
[X]
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
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Office 365 Training for Admins - 7 Day Trial

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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 Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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…

721 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