Solved

IF statement using AND in Excel 2010

Posted on 2014-03-15
6
335 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
  • 3
  • 2
6 Comments
 
LVL 48

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 48

Accepted Solution

by:
dbrunton earned 500 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
Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

 
LVL 40

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 48

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Suggested Solutions

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

860 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