?
Solved

IF statement using AND in Excel 2010

Posted on 2014-03-15
6
Medium Priority
?
352 Views
Last Modified: 2014-03-15
I am very frustrated with the syntax needed in Excel 2010 to perform a simple IF AND statement. The following formula works flawlessly if K4>0.

=IF(K4>0,(O27 + (E13/2) + E25),(O27 + (E13/2) + E25 + P2))

I need to add the logic to make the final result be 0 if K4 =0.
Any and all help will be greatly appreciated.
0
Comment
Question by:cfrake
[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
  • 3
  • 2
6 Comments
 
LVL 49

Expert Comment

by:dbrunton
ID: 39932137
Try

=IF(K4>0,(O27 + (E13/2) + E25), IF(K4=0, 0,(O27 + (E13/2) + E25 + P2)))

and

=IF(K4>0,O27 + (E13/2) + E25,IF(K4=0, 0,O27 + (E13/2) + E25 + P2))

might do it as well.  I've taken out brackets that may not be necessary.
0
 

Author Comment

by:cfrake
ID: 39932176
Thank You,
I think my question was poorly presented.
The AND has to do with if P2 is added or not and is based on another cell (N4).
Pseudo code:
If K4 >0 AND N4<3 Then (O27 + (E13/2) + E25)
ELSE IF K4>0 AND N4=3 then (O27 + (E13/2) + E25 + P2
ELSE 0.

All cells other than K4 are always populated and the original formula is good unless K4=0.
Because all other cells are populated the result is always greater than 0.
If K4=0 the end result needs to be 0.
Thank you again for the response and i hope that clarifies the question.
0
 
LVL 49

Accepted Solution

by:
dbrunton earned 2000 total points
ID: 39932194
Try this

=if(and(k4>0,n4<3),O27+(E13/2)+E25,if(and(k4>0,n4=3),O27+(E13/2)+E25+p2,0))
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 41

Expert Comment

by:Sharath
ID: 39932198
try this.

=IF(K4>0, IF(N4<= 3, O27 + (E13/2) + E25,0) + IF(N4=3,P2,0),0)
0
 
LVL 49

Expert Comment

by:dbrunton
ID: 39932201
Probably this one

=if(k4=0,0,if(and(k4>0,n4<3),O27+(E13/2)+E25,if(and(k4>0,n4=3),O27+(E13/2)+E25+p2,0)))

Note that if k4 >0 and n4>3 it will also return 0.
0
 

Author Closing Comment

by:cfrake
ID: 39932205
PERFECT!!
 I will have to tweak some cell references finish up the project now but you have provided me with the syntax required.
I cannot thank you enough.
I wish I would have posted this question here before wasting all that time hammering at it on my own.
Thank you,
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

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

765 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