Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

IF statement using AND in Excel 2010

Posted on 2014-03-15
6
Medium Priority
?
359 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
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 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.

598 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