Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Nested IF AND statements in Excel

Posted on 2013-05-13
7
Medium Priority
?
391 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
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

963 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